Improve VBA Code - Automatically Hide Rows

tlc53

Active Member
Joined
Jul 26, 2018
Messages
380
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.

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

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if this is a little faster

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I see that I also started the range at L2, this line should be changed as follows
VBA Code:
Set rng = Range("13Cells(Rows.Count, 12).End(xlUp)).SpecialCells(xlCellTypeVisible)
And his line.
VBA Code:
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

Active Member
Joined
Jul 26, 2018
Messages
380

ADVERTISEMENT

I see that I also started the range at L2, this line should be changed as follows
VBA Code:
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..

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

Active Member
Joined
Jul 26, 2018
Messages
380
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..

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Glad you could mend my typos and make it work,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,685
Members
415,920
Latest member
ExcelNoob28

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