I am using VBA in Excel to clean up an imported CSV file with nearly 10000 rows of data.
Sometimes the procedure runs to completion without hanging:) Other times it gets about 6% through the rows and Hangs:( Still other times it might make it to 50% or 80% or some other apparently arbitrary percentage before hanging:confused:
When it hangs my only recourse is to hit the Close icon (“x” at top-right of the Exel window) which brings up the helpful (I don’t think) “Microsoft Excel is Not Responding” dialogue with the following report:
[INDENT]Description:[/INDENT]
[INDENT]A problem caused this program to stop interacting with Windows.[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Problem signature:[/INDENT]
[INDENT]Problem Event Name: AppHangB1[/INDENT]
[INDENT]Application Name: EXCEL.EXE[/INDENT]
[INDENT]Application Version: 14.0.7168.5000[/INDENT]
[INDENT]Application Timestamp: 56eade71[/INDENT]
[INDENT]Hang Signature: 51b9 [/INDENT]
[INDENT]Hang Type: 256[/INDENT]
[INDENT]OS Version: 6.1.7601.2.1.0.256.48[/INDENT]
[INDENT]Locale ID: 3081[/INDENT]
[INDENT]Additional Hang Signature 1: 51b9b6073c36dc56d3f2228e63c51b26[/INDENT]
[INDENT]Additional Hang Signature 2: 9712[/INDENT]
[INDENT]Additional Hang Signature 3: 9712c99c6300efb4d926c2dbcbed9b2d[/INDENT]
[INDENT]Additional Hang Signature 4: 51b9 [/INDENT]
[INDENT]Additional Hang Signature 5: 51b9b6073c36dc56d3f2228e63c51b26 [/INDENT]
[INDENT]Additional Hang Signature 6: 9712 [/INDENT]
[INDENT]Additional Hang Signature 7: 9712c99c6300efb4d926c2dbcbed9b2d[/INDENT]
Below is the VBA procedure I’m running. I initially tested it with about 500 rows of data and it worked perfectly every time. It’s only when I import my large csv data file with about 10,000 rows that the hanging occurs.
Public Sub CleanCSVData()
Dim iLastRow As Long
Dim sht As Worksheet
Dim iRowCount As Long
Dim iColumnCount As Long
Dim Msg, Button, Title, Response As String
On Error GoTo ErrorMessages
Button = vbExclamation
Title = “Public Sub CleanCSVData()…”
On Error GoTo ErrorMessages
‘ Turn OFF Screen updating
Application.ScreenUpdating = False
‘ **** Determine Last Row number
Set sht = Worksheets(“CSV Input Data”)
iLastRow = sht.Cells(sht.Rows.Count, “A”).End(xlUp).Row
‘ Check is Ephemeris is Heliocentric or Geocentric
For iRowCount = 1 To 20
If sht.Range(“A” & iRowCount) = “heliocentric” Then
Sheets(“Instructions & Summary”).Range(“ZodiacCentre”).Value = “Heliocentric”
Exit For
Else
Sheets(“Instructions & Summary”).Range(“ZodiacCentre”).Value = “Geocentric”
End If
Next
‘ **** Start data cleanup process
For iRowCount = 1 To iLastRow
‘ Test for unnecessary data in row
With sht
Select Case .Range(“A” & iRowCount)
Case “SWISS”
.Range(“A” & iRowCount & “:” & “Z” & iRowCount).ClearContents
Case “heliocentric”
.Range(“A” & iRowCount & “:” & “Z” & iRowCount).ClearContents
Case “Delta”
.Range(“A” & iRowCount & “:” & “Z” & iRowCount).ClearContents
Case “page”
.Range(“A” & iRowCount & “:” & “Z” & iRowCount).ClearContents
End Select
If Left(.Range(“A” & iRowCount), 3) = “D:” Then .Range(“A” & iRowCount & “:” & “Z” & iRowCount).ClearContents
If Trim(.Range(“B” & iRowCount)) = “Sid.t” Then
.Range(“B” & iRowCount & “:” & “M” & iRowCount).Cut Destination:=.Range(“E” & iRowCount)
End If
If .Range(“A” & iRowCount) = “Day” Then
.Range(“A” & iRowCount).Cut Destination:=.Range(“B” & iRowCount)
End If
If Len(.Range(“A” & iRowCount)) = 3 And UCase(.Range(“A” & iRowCount)) “MAY” Then
.Range(“B” & iRowCount & “:” & “O” & iRowCount).Cut Destination:=.Range(“C” & iRowCount)
.Range(“B” & iRowCount).Value = Trim(Right(.Range(“A” & iRowCount), 2))
.Range(“A” & iRowCount).Value = Left(.Range(“A” & iRowCount), 1)
End If
.Range(“B” & iRowCount).Value = Trim(.Range(“B” & iRowCount).Value)
For iColumnCount = 6 To 15
If Len(Trim(.Cells(iRowCount, iColumnCount))) = 3 And _
Not Trim(.Cells(iRowCount, iColumnCount)) = “Terra” Then
.Cells(iRowCount, iColumnCount).Value = Trim(.Cells(iRowCount, iColumnCount) & “’00”)
Else
.Cells(iRowCount, iColumnCount).Value = Trim(.Cells(iRowCount, iColumnCount))
End If
Next
End With
‘ Clear Clipboard after Cut & Paste
Application.CutCopyMode = False
‘ Display Processing progress in Status Bar
Application.StatusBar = “Clean CVS Data Progress: ” & iRowCount & ” of ” & iLastRow & “: ” & Format(iRowCount / iLastRow, “0%”)
Next
‘ Clear Status Bar Progress Status
Application.StatusBar = False
‘ Turn ON screen updating
Application.ScreenUpdating = True
Exit Sub
ErrorMessages:
Msg = “Error #: ” & Str(Err.Number) & ” was generated by ” & Err.Source & vbCrLf & _
“Error Line: ” & Erl & vbCrLf & _
Err.Description
Response = MsgBox(Msg, Button, Title)
End Sub
The hanging occurs when running through the “For iRowCount = 1 To iLastRow …. Next” loop.
I have a similar procedure that that runs through the same sheet/rows as the above procedure to modify the data and write it to a new sheet which has never hung up on me:)
Incidentally I’m running MS Office 2010 32 bit under Windows 7 Ultimate 64 bit (both fully updated).
I hope that some-one has a clue as to what’s going on and how to fix it.
Thanks in anticipation.
BygAuldByrd
