VBA - hide all rows unless column F contains specific text in cell

[strongman86]

Hi Lads,

I have macro below which hides all rows and leaves all rows with text ''APPLE'' in F column on Sheet2, it works ok, but the problem is that in this table all cells are referrencing to different sheet i.e. ='Sheet1'!A1 which means Marco hides all rows not containing APPLE and all the rest of my around 1000 rows with reference formulas in them. I there anything I can add to code so it ignores ''Empty''/Reference cells? Thanks.

Sub hidesome()    Dim r As Long    Application.ScreenUpdating = False    ActiveSheet.DisplayPageBreaks = False    r = Range("F" & ActiveSheet.Rows.Count).End(xlUp).Row    For r = 2 To r        Select Case Cells(r, 6)            Case "APPLE"                Rows(r).Hidden = False            Case Else                Rows(r).Hidden = True        End Select    Next rEnd Sub

 

[Fluff]

How about

Sub hidesome()
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Range("A1:F1").AutoFilter 6, "apple", xlOr, ""

End Sub

 

[strongman86]

Thanks Fluffy. Works great. Couple more thing I would need: add second text ''banana'' if I add to code as I did on below it gives me Run time error. Also How can I make this macro automated so it runs every time workbook is closed.? Thanks.

Sub hidesome()
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Range("A1:F1").AutoFilter 6, "apple", "banana", xlOr, ""


End Sub

 

[Fluff]

How about

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Range("A1:F1").AutoFilter 6, Array("apple", "banana", ""), xlFilterValues

End Sub

 

[strongman86]

Unfortunately it's not working. When I save & close woorkbook and reopen: filters are not applied.

 

[Fluff]

What sheet do you want it to work on?

 

[strongman86]

Sheet No 2. Called ''LIST''

 

[Fluff]

Ok, how about

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
With Sheets("List")
   .DisplayPageBreaks = False
   .Range("A1:F1").AutoFilter 6, Array("apple", "banana", ""), xlFilterValues
End With

End Sub

 

[strongman86]

No still doesn't work.
When I change first row of code and do manually it works OK.

Sub hidesome()
Application.ScreenUpdating = False
With Sheets("List")
   .DisplayPageBreaks = False
   .Range("A1:F1").AutoFilter 6, Array("apple", "banana", ""), xlFilterValues
End With

End Sub

 

[Fluff]

Did you put the code in the ThisWorkbook module?