Improve VBA Code - Automatically Hide Rows

[tlc53]

Hi there,

I have a set of data which can change via formulas. To reduce the amount information the user is looking at, I wanted to hide the rows with a zero balance.

I went to test the below VBA code but it was taking so long, I had to stop it. As you can see, there's over 4,000 rows. Since the data can change when other worksheets are updated, I wanted to have the rows hide/unhide automatically when the sheet is selected.

Is there another way to do this which would make it operate quicker and easier? I wasn't able to test the below code (took too long) so I'm still not sure if it works as I was wanting it to.

Private Sub Worksheet_Activate()
   Dim Cl As Range
   For Each Cl In Range("L13:L4300")
     Cl.EntireRow.Hidden = IIf(Cl = 0, True, False)
   Next Cl
End Sub

Any help would be appreciated. Thanks!

 

[JLGWhiz]

See if this is a little faster

Private Sub Worksheet_Activate()
Dim rng As Range
Rows.Hidden = False
Range("L2", Cells(Rows.Count, 12).End(xlUp)).AutoFilter 1, "=0"
Set rng = Range("L2", Cells(Rows.Count, 12).End(xlUp)).SpecialCells(xlCellTypeVisible)
Sheet1.AutoFilterMode = False
rng.EntireRow.Hidden = True
End Sub

 

[tlc53]

See if this is a little faster

Thank you! It certainly was fast however, it's hidden rows 2:4300 which was not what I expected

 

[JLGWhiz]

I see that I also started the range at L2, this line should be changed as follows

Set rng = Range("13Cells(Rows.Count, 12).End(xlUp)).SpecialCells(xlCellTypeVisible)

And his line.

Range("L13", Cells(Rows.Count, 12).End(xlUp)).AutoFilter 1, "=0"

With that out of the way. Does any of the cells in column L have values greater than zero? If so, open the vb editor and step through the code using the F8 key to move line by line. If the code produces the desired results then, the problem could be timing which we can easily fix. Post back with results and further actions.

 

[tlc53]

I see that I also started the range at L2, this line should be changed as follows

Set rng = Range("13Cells(Rows.Count, 12).End(xlUp)).SpecialCells(xlCellTypeVisible)

It's reporting a compile error on this row. Expected list separator or )
So I amended the code as below but then it highlights this row and says Run-time error 1004..

Private Sub Worksheet_Activate()
Dim rng As Range
Rows.Hidden = False
Range("L13", Cells(Rows.Count, 12).End(xlUp)).AutoFilter 1, "=0"
Set rng = Range("13", Cells(Rows.Count, 12).End(xlUp)).SpecialCells(xlCellTypeVisible)
Sheet10.AutoFilterMode = False
rng.EntireRow.Hidden = True
End Sub

 

[tlc53]

It's reporting a compile error on this row. Expected list separator or )
So I amended the code as below but then it highlights this row and says Run-time error 1004..

Fixed it and it works a charm now..

Private Sub Worksheet_Activate()
Dim rng As Range
Rows.Hidden = False
Range("L13", Cells(Rows.Count, 12).End(xlUp)).AutoFilter 1, "=0"
Set rng = Range("13", Cells(Rows.Count, 12).End(xlUp)).SpecialCells(xlCellTypeVisible)
Sheet10.AutoFilterMode = False
rng.EntireRow.Hidden = True
End Sub

Thank you!! I'll run more tests but it's working really fast

 

[JLGWhiz]

Glad you could mend my typos and make it work,
regards, JLG