Advanced Filter

[mbsalim]

Hi Guys,

New to the site, really stuck with this excel filter problem.

here goes;

I have a spreadsheet which is essentially a table, table headers are in C7:I7 and the table cells are C8:I71.

C8:I71 are all filled with a vlookup formula that pulls in information from other sheets. When the data is blank it returns the value 0, which is fine but is there a way (I'm sure advanced filter is the solution) to have the table automatically make the cells with 0 disappear (the condition would be set on column C only as other columns need have zero within text where as C column does not)

So basically where C8:I71 >0 it will show, when they are = 0 then not-shown.

THANK YOU!!!

 

[RoryA]

I'm not sure that answers my question. If the cells appear blank anyway, what is the benefit of hiding the rows?

 

[mbsalim]

The row numbers still come up (B8:B71) and the sheet has a border up to B8:I71 (largest set of data), the sheet is also in the pagebreak view

 

[RoryA]

Right-click the sheet tab, choose View Code and paste this in:

Private Sub Worksheet_Calculate()
    On Error GoTo oops
    Application.EnableEvents = False
    Me.Range("C7:I71").AutoFilter field:=1, Criteria1:="<>"
    
oops:
    Application.EnableEvents = True
End Sub

 

[mbsalim]

thank you soooo much!!!....Legend!!!

 

[RoryA]

Glad to help.