Code runs slow. Any suggestions?

snowboy001

Board Regular
Joined
Dec 5, 2009
Messages
100
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.

Code:
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

btomjack

Board Regular
Joined
Sep 13, 2013
Messages
229
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-known Member
Joined
Mar 9, 2012
Messages
951
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
"SpecialCells" method will probably be faster.

Gary

Code:
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

Board Regular
Joined
Dec 5, 2009
Messages
100
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

Board Regular
Joined
Dec 5, 2009
Messages
100
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:

Code:
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,385
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top