Hide/unhide rows depending on their vaule

[allovera]

I have this Macro to hide/unhide rows depending on the first column value.

The problem is that it examines row by row and it takes very long since the values on the columns keep changing depending on a formula.
Is there a way to examine all rows at the same time and hide/unhide them in a second. Or at the same rate that the data updates?

-----------------------------------------------------------
Sub HideRows()
BeginRow = 11
EndRow = 59
ChkCol = 3


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False

End If
Next RowCnt
End Sub
----------------------------------------------------

 

[Drrellik]

I took your short code and placed it on a sheet with very little data and no formulas, it runs fast and clean.

not sure why having formulas in your sheet would slow down the code, the range is not being deleted so you show not be having any REF# errors.

 

[allovera]

It doesn’t run that slow. It takes about 5 sec for the 48 rows in the example...

But I will use the same code on another excel sheet with 1000+rows and was just wondering if there is an alternative that could examine all rows at the same time instead of 1 by 1…

Since I need it to update as fast as possible.

 

[Drrellik]

I am not sure, I think the way you are doing it is pretty fast considering.

Don

 

[Caleeco]

try turning off screen updating while the code runs, should speed it up slightly

Sub HideRows()


Application.ScreenUpdating = False
BeginRow = 11
EndRow = 59
ChkCol = 3


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False


End If
Next RowCnt


Application.ScreenUpdating = True
End Sub

 

[Caleeco]

Alternatively, you could add a filter to the table. Then a bit of VBA can be written to apply a filter which removes rows where the value in 'ChkCol' is blank.

Let me know if this method is of interest and I can write the code for it

 

[skywriter]

You may want to try:

Application.EnableEvents = False
Application.EnableEvents = True

Put the first line before your loop and the second line after your loop.

I found out the hard way that hiding rows can trigger the calculate event, in certain instances, maybe all I don't know for sure, but depending on your sheet calculating every time a row is hidden could cause a noticeable speed difference in your code.

 

[allovera]

Thx Caleeco,

That help with the annoying screen refresh while each row is evaluated. But it didn’t speed it up. I might keep it with this change. Thx!

 

[Caleeco]

Thx Caleeco,

That help with the annoying screen refresh while each row is evaluated. But it didn’t speed it up. I might keep it with this change. Thx!

No problem. Given what skywriter suggested, this may help further:

With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
 End With

BeginRow = 11
EndRow = 59
ChkCol = 3

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False

End If
Next RowCnt

With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With

 

[allovera]

Thx Caleeco and Skywriter

That actualy help.. it takes the same time if its 5 or 1000 rows.

Thx for the help!!