Hide Row depending on Cell Value

[Jim McEwan]

Hi There,

I would like to run a macro that searches column [AS] and hides any rows in that column that contain "0", can anyone help?

Thanks

JM

[stnkynts]

Try this:

Sub Hide_AS()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1") 'you may need to change this to match your sheet name
Dim rCell As Range

Application.ScreenUpdating = False
ws.UsedRange.EntireRow.Hidden = False

For Each rCell In ws.Range("AS1:AS" & ws.Range("AS" & Rows.Count).End(xlUp).Row)
    If rCell.Value = "0" Then
        rCell.EntireRow.Hidden = True
    End If
Next rCell

Application.ScreenUpdating = True

End Sub

[Jim McEwan]

Hi stnkynts,

This works in theory - although I have over 3000 rows to check, so this way would take and absolute age to execute. Do you know of any faster ways?

Regards,

JM

[JOHN H. DAVIS]

Does this help?

Sub JimMcEwan()
Application.ScreenUpdating = False
Range("AS1:AS" & Range("AS" & Rows.count).End(3)(1).Row).AutoFilter 1, "=0"
Application.ScreenUpdating = True
End Sub

[stnkynts]

Yeah, I could do it by autofilter, advancedfilter, reg expression. I wouldn't think the code would take that long to run though. How long did it take on your workbook?

[stnkynts]

@John, he would need to hide the 0's

Range("AS1:AS" & Range("AS" & Rows.count).End(3)(1).Row).AutoFilter 1, "<>0"

[JOHN H. DAVIS]

@John, he would need to hide the 0's

Range("AS1:AS" & Range("AS" & Rows.count).End(3)(1).Row).AutoFilter 1, "<>0"

Oops. Good catch.