Hiding lots of rows based on cell values

[glad_ir]

Hi,

I’m new to VBA, as you’ll guess from my question, and am hoping somebody can help please.

I’m trying to hide a large number of rows based on values in 1 column - ie if A1 = 1 or 2 then hide row 1, if A2 = 1 or 2 then hide row 2 and so on down the rows.

The code below works but the thought of replicating it for a couple of hundred rows isn’t filling me with joy!!! Please can somebody put me out of my misery?

If range(“A1”) = 1 Then Rows(“1:1”).EntireRow.Hidden=True
If range(“A1”) = 2 Then Rows(“1:1”).EntireRow.Hidden=True

Any help is very much appreciated. Thank you

 

[cortexnotion]

Sub HideRows()

Dim Cell As Range, Value as Long, Count As Long

With ThisWorkbook.Sheets("Sheet1")
        Value = 1       
        Count = 0
                For Each Cell In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
                        If Cell.Value >= 1 And Cell.Value <= Value Then
                                Cell.EntireRow.Hidden = True
                                Value = Value + 1
                                Count = Count + 1
                        End If
                Next Cell
        MsgBox Count & " rows hidden"
End With

End Sub

 

[cortexnotion]

I may have misread your criteria, are you looking for either 1 or 2 to be found in all cells in Column A, or does the value increase to 3,4,5 etc?

 

[glad_ir]

Hi Cortexnotion.
Thank you for replying. I'm looking to apply the same rule in each row - i.e. if there is a 1 or 2 in the rows column A cell then I'd like the row to be hidden.
Thank you

 

[cortexnotion]

Thanks for confirming - in that case try...

Sub HideRows()

Dim Cell As Range, Count As Long

With ThisWorkbook.Sheets("Sheet1")
        Count = 0
        For Each Cell In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
                If Cell.Value = 1 Or Cell.Value = 2 Then
                        Cell.EntireRow.Hidden = True
                        Count = Count + 1
                End If
        Next Cell
        MsgBox Count & " rows hidden"
End With

End Sub

 

[glad_ir]

That is perfect! Thank you so much. I understand most of what you've done here but need to do some research on As Long.
thanks again.

 

[Michael M]

Maybe this way

Sub MM1()
Dim r As Long
For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Cells(r, 1) = 1 Or Cells(r, 1) = 2 Then Rows(r).EntireRow.Hidden = True
Next r
End Sub

 

[Rick Rothstein]

Assuming Column A contains constants (that is, no formulas), you could also use this non-looping macro as well...

Sub Hide1sAnd2s()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .EntireRow.Hidden = False
    .Value = Evaluate(Replace("IF(@=1,""=1"",IF(@=2,""=2"",If(@="""","""",@)))", "@", .Address))
    .SpecialCells(xlFormulas).EntireRow.Hidden = True
    .Replace "=", "", xlPart, , , , False, False
  End With
End Sub

 

[glad_ir]

Sub MM1() Dim r As Long For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1 If Cells(r, 1) = 1 Or Cells(r, 1) = 2 Then Rows(r).EntireRow.Hidden = True Next r End Sub

Many thanks Michael - this works great. Totally agree with the quote as well! Thank you

 

[glad_ir]

Sub Hide1sAnd2s() With Range("A1", Cells(Rows.Count, "A").End(xlUp)) .EntireRow.Hidden = False .Value = Evaluate(Replace("IF(@=1,""=1"",IF(@=2,""=2"",If(@="""","""",@)))", "@", .Address)) .SpecialCells(xlFormulas).EntireRow.Hidden = True .Replace "=", "", xlPart, , , , False, False End With End Sub

this also works a treat as well! Thank you Rick