Hide zero values in a colums

[MichaelWayne_71]

Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I have completed my proposal, I would like to click "shrink list" to hide all ROWS that have "0" (zero) as it's value. And then be able to click "expand list" to show all rows again. Essentially, the macro will look only at column A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for less knowledgeable users.

Can someone help?

 

[tactps]

Record the macros that you need (tools, macro, record)

 

[Nimrod]

This procedure will Toggle between hiding and unhiding rows based on rows that have the value "0" in col A of the activesheet.

Warning:
Cells IV1:IV2 are used as a temp staging area for advanced fitler criteria. If you have values in these cells they will be removed.

Public FilterOn As Boolean
Sub ToggleZeroRows()

Dim CritRange As Range
Set CritRange = Range("IV1:IV2")

 If FilterOn Then
    ActiveSheet.ShowAllData
    FilterOn = False
 Else
 
    'set up criteria for advanced filter
    CritRange.ClearContents
    CritRange(2).Formula = "=NOT(A2=0)"
    
    'hide using advanced filter
        Range("A1:Z65536").AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=CritRange
        
    'clear Range
    CritRange.ClearContents
    FilterOn = True
       
End If
End Sub

 

[MichaelWayne_71]

You are awesome!!!

Thank you!!!

 

[MichaelWayne_71]

You are awesome!!!

Thank you!!!