Macro running extremely slow

[tyeung]

Hi guys,

Nevermind, I think I've solved the problem. The reason the macro was running so slow was because the file size had ballooned to 13 MB due to(I think) Excel thinking there were a lot of "data" cells when they were just blank. I went through and deleted all rows and columns outside my data, and the file shrunk back down to 100 KB.

Now the code runs uber-quick.

[jpjones23]

Just in case, Excel seems to like to have its cells balloon up due to formatting. I developed a macro that clears the formatting. It's manual and asks for the start row and column simply because I always have different starting points in each worksheet. Here's the macro. Maybe it will help you to let the air out of your excel balloon.

Jeff Jones
[VBA]
Sub ClearFormatting()
'
' ClearFormatting Macro
' Macro created 8/12/2005 by Jeff jones
'
Dim varStartRow As Variant
Dim strStartCol As String
Dim strRowRange As String
Dim strColRange As String
Dim DirInp As String
Dim Title As String
Dim Default As String
Dim Msg As String
Const lngLastRow = 65536
Const strLastCol = "IV"
On Error GoTo HandleError
'Stop
' Work with rows
Msg = "Enter the First Row to Select"
Title = "Designated Row"
Default = "Please enter Row Number"
varStartRow = InputBox(Message, Title, Default)
If Not IsNumeric(varStartRow) Then
MsgBox "Invalid data was entered. Processing halted."
Exit Sub
End If
strRowRange = varStartRow & ":" & lngLastRow
Rows(strRowRange).Select
Selection.ClearFormats
Selection.ClearFormats
Selection.Delete Shift:=xlUp
Selection.ClearFormats
Selection.ClearFormats
Selection.Delete Shift:=xlUp
Selection.ClearFormats
Selection.ClearFormats
Selection.Delete Shift:=xlUp
Range("A1").Select
'Work with columns
Msg = "Enter the First Column to Select"
Title = "Designated Column"
Default = "Please enter Column Number"
strStartCol = InputBox(Message, Title, Default)
If strStartCol = "" Then
MsgBox "No data was entered. Processing cancelled."
Exit Sub
End If
strColRange = strStartCol & ":" & strLastCol
Columns(strColRange).Select
Selection.ClearFormats
Selection.ClearFormats
Selection.Delete Shift:=xlToLeft
Selection.ClearFormats
Selection.ClearFormats
Selection.Delete Shift:=xlToLeft
Selection.ClearFormats
Selection.ClearFormats
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Exit Sub
HandleError:
'Stop
'Resume
If Err = 13 Then
Title = "Unexpected Error"
MsgBox "An invalid value was entered into the InputBox. Either the row number was too large or the column value was invalid. Processing halted"
Else
Msg = "Error number = " & Err & ", " & Error & ". Do you want to continue ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Unexpected Error"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Resume Next
Else
Exit Sub
End If
End If

End Sub
[/VBA]

[tyeung]

Thanks for that, I could definitely use this!

[jpjones23]

You are very welcome.