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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,292
Messages
5,836,451
Members
430,431
Latest member
Tomexcel2022

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