0

I am trying to hide all the rows if the B column value has the value "x". This is what I have so far:

Public Sub HideRowsOOS()

    Application.ScreenUpdating = False

    With ActiveSheet
    For Each cell In Range("B2:B")
        If cell.Value = "x" Then
            cell.EntireRow.Hidden = True
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
t l n
  • 25
  • 4
  • "B2:B" is not a valid range reference. The normal approach is to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then include that in your reference. – BigBen Apr 30 '20 at 13:20
  • Note that you are not referring to the `With ActiveSheet`... you would need to add a period `.` in front of the `Range` call to actually do so. – BigBen Apr 30 '20 at 13:23

2 Answers2

1

I will offer this optimized procedure:

Option Explicit

Public Sub HideRowsOOS()

    Application.ScreenUpdating = False

    With Worksheets("mySheet") 'change as neeeded

        Dim lastRow as Long
        lastRow = .Cells(.Rows.Count,2).End(xlUp).Row

        For Each cell In .Range("B2:B" & lastRow)

            If cell.Value = "x" Then 

                Dim collect as Range
                If collect Is Nothing Set collect = cell: Else Set collect = Union(collect, cell)

            End If

        Next cell

    End With

    collect.EntireRow.Hidden = True

End Sub
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
0

Replace:

Range("B2:B")

with:

Intersect(Range("B:B"),ActiveSheet.UsedRange)

(you could also remove the With statement)

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • just to be clear, `.UsedRange` can have unintended consequences and significanty affect performance if the are cells that appear to be blank but house formatting and such in areas where there is no data. – Scott Holtzman Apr 30 '20 at 16:35
  • @ScottHoltzman You are correct. The suggestion may test some extra cells unnecessarily, but its better than test the entire column **B** – Gary's Student Apr 30 '20 at 17:06