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

 

[Caleeco]

Thx Caleeco and Skywriter

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

Thx for the help!!

That's great :D You're welcome, Thanks for the feedback!

 

[allovera]

I made it work faster with this code, if you are interested.
Instead of hiding each row 1 by 1, I select all cells in a range with a specific value and hide the rows all at once. (The range if from cell A11:A149)

Sub HideRows()    
    Application.ScreenUpdating = False
    
    Range(Cells(11, 1), Cells(149, 1)).Select
        Selection.EntireRow.Hidden = False
    
    Range(Cells(11, 1), Cells(149, 1)).Select
        Selection.SpecialCells(xlCellTypeFormulas, 2).Select
        Selection.EntireRow.Hidden = True
    
    Application.ScreenUpdating = True
        
End Sub

 

[Caleeco]

^^ Nice!

Great improvement, i'll keep that in the memory bank for future :D

 

[skywriter]

Selecting ranges and activating sheets are two of the things known to slow down code.

There is no reason to select the ranges, just hide them or make them visible.

 Range(Cells(11, 1), Cells(149, 1)).EntireRow.Hidden = True

In the case of you being on one sheet and wanting to do something to another sheet, don't activate the sheet and select the range, just code directly.

Example:

With Worksheets("Sheet1")
    .Range(.Cells(11, 1), .Cells(149, 1)).EntireRow.Hidden = False
    .Range(.Cells(11, 1), .Cells(149, 1)).EntireRow.Hidden = True
End With

 

[allovera]

I love how each time I do something and think it’s the best way to do it, someone shows me an even better way.

Thx Skywriter and Caleeco. This is the way to keep learning and improving our work!

Thx for the help

 

[skywriter]

I used to think the same thing.

We are fortunate when someone takes the time to show us the alternatives.

I posted something just the other day that I thought was as efficient as it could possibly be.

Now I have to pick apart the other code posted after mine to figure out how it works, but I know I will learn something.

Good Luck.

I love how each time I do something and think it’s the best way to do it, someone shows me an even better way.