Hiding Columns with Zero Values

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,572
Office Version
  1. 2021
Platform
  1. Windows
I need to write VBA code to hide row where the value is zero (Column B) as well as the 1 st row on the left of the zero value . See Example below


Column A Column B
UNITS SOLD MAZDA 323 0
UNITS SOLD MARATHON 0
UNITS SOLD MAZ B SER 3

The rows housing the below units with zero values must be hidden
UNITS SOLD MAZDA 323 0
UNITS SOLD MARATHON 0

Yours assistance will be most appreciated

Howard
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
assuming your Col B has just the numbers
try
Sub DelZeroRows()
'
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="0"
Selection.EntireRow.Delete
Range("A1").Select
End Sub
 
Upvote 0
howard

I think BruceyBonu$'s suggestion needs some modification.

1. If you don't already have headings, I suggest that you insert headings in row 1 with your data starting at row 2
2. I suggest that you try this slightly modified code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> HideZeroRows()
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.AutoFilter Field:=2, Criteria1:="<>0"
    Range("A1").Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Thanks for your help.

If the zero values change to values greater than zero at a later stage will these columns unhide or will I have to write code to unhide?

If so, will you please provide the code

Thanks

Howard
 
Upvote 0
Thanks for your help.

If the zero values change to values greater than zero at a later stage will these columns unhide or will I have to write code to unhide?

If so, will you please provide the code

Thanks

Howard
I assume from this that the values in column B are the result of formulas. Is there a particular range of cells the user changes that cause the results in column B to change? Where I am headed is to be able to say (in code) something like: if cells x or y or z change then re-filter on column B.
 
Upvote 0
Hi Peter

The values in column B are linked to another worksheet. It is possible for the values to change in another period. For eg the vulues that I have now are for August, but could change in Sept, Oct etc.


Howard
 
Upvote 0
Are there other changeable cells in this sheet besides what is in column B?
 
Upvote 0
No, there are only changeable cells in column B

Howard
 
Upvote 0
Perhaps you could use the code with the Worksheet_Activate event. Each time the sheet is activated, the filter will be re-run. Is that any use?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.AutoFilter Field:=2, Criteria1:="<>0"
    Range("A1").Select
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,217,758
Messages
6,138,438
Members
450,137
Latest member
HANHAN

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
Back
Top