Code runs slow. Any suggestions?

[snowboy001]

Forum,

This piece of code seems to run extremely slow. Does anyone have any ideas on how this could be optimized to run faster? I need it to run each time the sheet is activated, and eventually there will be several thousand lines in the sheet with data in them.

Private Sub Worksheet_Activate()
'Unhides all rows then hides empty rows so that only active Action Items can be seen


Application.ScreenUpdating = False


Dim i, LastRow
Dim ActionList As Worksheet


Set ActionList = Worksheets("Action Item List")


Rows.Hidden = False


LastRow = ActionList.Range("A" & Rows.Count).End(xlUp).Row


For i = LastRow To 1 Step -1
    If Cells(i, "C") = "" Then
        Cells(i, "C").EntireRow.Hidden = True
    End If
Next i


Application.ScreenUpdating = True


End Sub

Thanks!

 

[btomjack]

When you declare your variables i and LastRow, you don't specify a type. I could be mistaken, but I think this defaults them to type Variant. It looks like i could be changed to variable type Long, and this might reduce the memory necessary to run your code.

 

[pplstuff]

Well, it's going slow because you're looping through tons of rows.

My suggestion would be to use .autofiltermode and filter to the blanks. Then select all visible cells. Then hide the selection. Then clear the autofilter.

It might not like hiding rows that aren't sequential, so you may have to sort the list A-Z and then hide all visible rows. Let me know if you need help with code too.

 

[Gary McMaster]

"SpecialCells" method will probably be faster.

Gary

Private Sub Worksheet_Activate()

'Unhides all rows then hides empty rows so that only active Action Items can be seen

Application.ScreenUpdating = False

Dim i, LastRow
Dim ActionList As Worksheet

Set ActionList = Worksheets("Action Item List")

Rows.Hidden = False

ActionList.Range("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

 

[snowboy001]

Thanks for the suggestions everyone. I have to step out to a meeting quick, but I will report back with findings in a little while.

 

[snowboy001]

Thanks again for the suggestions.

btomjack: Declaring the variables as long did not give any noticeable improvement.

pplstuff: This seemed to work at first, but rows were becoming visible later in the process when they shouldn't have. I could have done a work around for this, but I ended up using the below code.

Gary: Your code errored out on the ActionList.Range("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True line. Not sure why...

Here is the code that got me what I needed:

Private Sub Worksheet_Activate()
'Unhides all rows then hides empty rows so that only active Action Items can be seen


Application.ScreenUpdating = False


Dim i, LastRow As Long
Dim ActionList As Worksheet


Set ActionList = Worksheets("Action Item List")


Rows.Hidden = False


ActionList.ListObjects("Table1").Range.AutoFilter field:=3, Criteria1:="<>"


Application.ScreenUpdating = True


End Sub