Hide zero values in a colums

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Record the macros that you need (tools, macro, record)
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
This procedure will Toggle between hiding and unhiding rows based on rows that have the value "0" in col A of the activesheet.

:eek: Warning: :eek:
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top