Search code examples
excelvbarangefiltering

Hide rows in an already filtered ranged based on two conditions for two different columns


I know how to do this with one condition, in fact I am already using it in my macro:

Dim cl, dl As Range, LR As Long, ws As Worksheet

Set ws = ActiveSheet
LR = ws.Range("A" & Rows.Count).End(xlUp).Row

For Each cl In ws.Range("AK2:AK" & LR).SpecialCells(xlCellTypeVisible)
     If cl.Value < 10 Then cl.EntireRow.Hidden = True
Next cl

Next, I would need to hide some more rows but based on two criteria this time. If column AL contains the value PASS, then I need to look at column H and hide all the rows that have a value different from 1 there. Is this even possible? I tried to define a second range called dl, but it does not work. Below two of my unsuccessful attempts. Any help would be greatly appreciated.

LR = ws.Range("A" & Rows.Count).End(xlUp).Row

For Each cl In ws.Range("AL2:AL" & LR).SpecialCells(xlCellTypeVisible)
    If cl.Value = "PASS" Then Set dl = ws.Range("H2:H" & LR).SpecialCells(xlCellTypeVisible)
       If dl.Value <> 1 Then dl.EntireRow.Hidden = True
       End If
Next cl
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
For Each cl In ws.Range("H2:H" & LR).SpecialCells(xlCellTypeVisible)
    Set dl = ws.Range("AL2:AL" & LR).SpecialCells(xlCellTypeVisible)
    If dl.Value = "PASS" And cl.Value = 1 Then cl.EntireRow.Hidden = True
Next cl

Solution

  • Using cl.Row to refer to the cell in column H in the same row as cl.

    For Each cl In ws.Range("AL2:AL" & LR).SpecialCells(xlCellTypeVisible)
         If cl.Value = "PASS" And ws.Cells(cl.Row, "H").Value <> 1 Then 
              cl.EntireRow.Hidden = True
         End If
    Next cl