hide rows based on value in specific column

Right now we have specific code to hide certain rows. It checks if in three different ranges if there is no value in a row and hides this row. This works very good.

However we now need to hide the rows when the value in column 5 of a row is equal to "0,00". The ranges where we need to check this do not have to be changed.
I already tried a lot of different methods, but can't get it working.

The current code is:

Sub HideEmptyRows()
    
    rnANVAjaar.Select
    rnANVAjaar.Unprotect
 
    Dim R1, R2, R3, AR, C As Range
         
    Set R1 = Range("A5:A24")
    Set R2 = Range("A33:A37")
    Set R3 = Range("A46:A50")
    
    Set AR = Union(R1, R2, R3)
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
        
    For Each C In AR
        If Len(C.Text) = 0 Then
            C.EntireRow.Hidden = True
        Else
            C.EntireRow.Hidden = False
        End If
    Next C
    
    rnANVAjaar.Protect
         
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub

Can somebody help me out.

Thanks in advance.

Berend-Jan

Administrative Note:


We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Thanks, amended my post with the correct tags

Hi

This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

The fastest, most efficient and elegant way for this sort of stuff is to use an Autofilter to filter the rows you're interested in.
Then use syntax like

Range("your_range").SpecialCells(xlCellTypeVisible).   then whatever you're trying to do..Copy, delete, etc

i.e.deal with a whole block of rows with one instruction rather than many as you process a loop

Untested in the absence of a workbook but something like

Sub HideRows
   Sheet1.Range("E1:E1000").Rows.Hidden = False
   Sheet1.Range("E1:E1000").AUtofilter, Field:=1, Criteria1:= 0
   Sheet1.Range("E1:E1000").Specialcells(xlCellTypeVisible).RowHeight=0
End Sub

Change the row range as appropriate, or better still create a dynamic range name for the rows and use the range name

Hi,

I don't see why the block hide as I described earlier wouldn't work. Sometimes it may be necessary to use a helper column with a compound formula that identifies whether a particular row should be hidden.

However please upload the actual workbook. Pictures are rarely much use. We need the workbook if only to test a solution.