Macro for Hiding Large Amount of Rows (2000+) based on cell value

[AWJ0603]

Hello All,

I’ve tried numerous approaches to this problem and the macro still works far too slowly. I am trying to develop a macro that takes the range A16:A2015 and hides the row if the cell value=1 and leaves it if the cell value=0. Ive tried the following:

Sub test()
Dim Last_Row As Long, n As Long

Application.ScreenUpdating = False


Last_Row = Range("A16").End(xlDown).Row

For n = 5 To Last_Row
If Cells(n, 6).Value = 1 Then
Cells(n, 6).EntireRow.Hidden = False
Else: Cells(n, 6).EntireRow.Hidden = True
End If
Next n

Application.ScreenUpdating = True

End Sub

But it still takes too long

 

[wrightyrx7]

Not sure why its taking so long i took the same approach and had lots of dummy data and it took a second or 2.

Sub test()
    Dim lastRow As Long
    Dim cell As Range
Application.ScreenUpdating = False


    With Worksheets("Sheet1")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For Each cell In Range("A16:A" & lastRow)
            If cell.Value = 1 Then
                cell.EntireRow.Hidden = True
            End If
        Next
    End With
Application.ScreenUpdating = True
End Sub

 

[AWJ0603]

Thanks Wrightyrx for the response. That ran slightly faster but still took 3 minutes and 42 seconds to run through. My version took around 4m 30. Do you have any ideas on how to maybe identify a range from certain point and then hide the rest? The 1's and 0's will be sequential for example: Cells A16:A250 may be 0's (Not to be hidden) but as soon as it changes to a 1 (to be hidden) on cell A:251, the remaining A251:A2015 will always be 1's.

 

[wrightyrx7]

You could run code on a Worksheet_Change if any row in column A is change it will check what it has changed to and decide whether to hide the row.

Right click on your worksheet and click 'view code' then paste the below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then 'column A
        If Target.Value = 1 Then Target.EntireRow.Hidden = True
    End If
End Sub

With regards to your original query, you could filter to only show rows with a 0 in column A with VBA


Example

Sub Test3()
With ActiveSheet
    .AutoFilterMode = False
    .Range("A16").AutoFilter
    .Range("A16").AutoFilter Field:=1, Criteria1:=0
End With

End Sub