SOLVED

Hide/Unhide Rows in Excel in multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2764774%22%20slang%3D%22en-US%22%3EHide%2FUnhide%20Rows%20in%20Excel%20in%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764774%22%20slang%3D%22en-US%22%3EI%20have%20a%20data%20in%20more%20than%2040%20sheets%20with%20same%20format.%20In%20%22Column%20AX%22%2C%20I%20have%20total%20of%20all.%20Data%20format%3A-%20%22top%20to%20bottom%20is%20date%20amd%20left%20to%20right%20is%20brands%22.%20In%20Column%20AX%20top%20to%20bottom%20is%20total%20of%20all%20brands.%20I%20want%20to%20hide%20those%20rows%20at%20once%20who%20has%20'Zero'%20values%20in%20rows.%20How%20can%20I%20do%20in%20multiple%20sheets%20at%20once.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2764774%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764838%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%2FUnhide%20Rows%20in%20Excel%20in%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1160249%22%20target%3D%22_blank%22%3E%40SomeshKaistha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhether%20the%20following%20macro%20works%20or%20not%20depends%20on%20the%20layout%20of%20your%20worksheets...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20HideZeros()%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20For%20Each%20wsh%20In%20Worksheets%0A%20%20%20%20%20%20%20%20Select%20Case%20wsh.Name%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20List%20the%20exceptions%20here%2C%20if%20any%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22Introduction%22%2C%20%22Summary%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'%20Skip%20these%20sheets%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20wsh.UsedRange.AutoFilter%20Field%3A%3D50%2C%20Criteria1%3A%3D%22%26lt%3B%26gt%3B0%22%0A%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20Next%20wsh%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
I have a data in more than 40 sheets with same format. In "Column AX", I have total of all. Data format:- "top to bottom is date amd left to right is brands". In Column AX top to bottom is total of all brands. I want to hide those rows at once who has 'Zero' values in rows. How can I do in multiple sheets at once.
6 Replies

@SomeshKaistha 

Whether the following macro works or not depends on the layout of your worksheets...

 

 

Sub HideZeros()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
        Select Case wsh.Name
            ' List the exceptions here, if any
            Case "Introduction", "Summary"
                ' Skip these sheets
            Case Else
                wsh.UsedRange.AutoFilter Field:=50, Criteria1:="<>0"
        End Select
    Next wsh
End Sub

 

best response confirmed by SomeshKaistha (New Contributor)
Solution
Thanks. I'll check & let you know whether it works for me or not.
Nott working for me
Issue is still there. Code not working for me

@SomeshKaistha 

Please attach a sample workbook without sensitive data.

@SomeshKaistha 

Personally, I would modify @Hans Vogelaar 's code to delete the rows, rather than filtering them, and then convert all the tabular data to Excel tables that resize to accommodate the actual content.