Hide rows that contain text

[Buskopan]

Hello! First of all thanks for useful website.

Need your help. I got spreadsheet with information in columns A to BL and In column "BK" on some rows i got message "closed" the rest within "BK" are with no text in it.

I need macro to hide the columns that contains "Closed" and something that will unhide them if required.

I will set that on the button.

Thank you in advance.

 

[Chris Mack]

Sub Hide()
    n = Cells(Rows.Count, "BK").End(xlUp).Row
    For r = 1 To n
        If Cells(r, "BK") = "Closed" Then Rows(r).Hidden = True
    Next
End Sub

Sub Unhide()
    Rows("1:" & Rows.Count).Hidden = False
End Sub

Hope this helps,

Chris.

 

[Buskopan]

I knew you can help me, but that a prompt reply was surprising. Thank you!

 

[Chris Mack]

Yw!

 

[Michael M]

Why not use Autofilter either with or without VBA
If you need VBA, and I think it will be quicker

Sub MM1()
    With Range("BK1")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>" & "Closed"
    End With
End Sub

Then remove the filter to unhide

 

[Buskopan]

Why not use Autofilter either with or without VBA
If you need VBA, and I think it will be quicker

Sub MM1()
    With Range("BK1")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>" & "Closed"
    End With
End Sub

Then remove the filter to unhide

Thank you for another option to solve this. But for some reason this macro gives me the error

Or I misunderstand something.

 

[Michael M]

Is there an autofilter already in use on the sheet ??
What error Msg do you get ???
I can't seem to replicate any error,....but try

Sub MM1()
    With Range("BK1")
        .AutoFilter
        .AutoFilter Field:=63, Criteria1:="<>" & "Closed"
    End With
End Sub

 

[Peter_SSs]

Thank you for another option to solve this. But for some reason this macro gives me the error

I'm not sure about your error, but there are certainly some circumstances where Michael's code will give unexpected results (ie not what you have asked for).

Here is another version of an AutoFilter method that you could try.

Sub Hide_Rows()
  With Range("BK1", Range("BK" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="<>Closed"
  End With
End Sub


Sub Show_Rows()
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

 

[Buskopan]

Here is another version of an AutoFilter method that you could try.

This one works just perfect and quick enough! Thank you!

 

[Peter_SSs]

This one works just perfect and quick enough! Thank you!

Good news.
Thanks for letting us know.