Hiding Columns with Zero Values

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,994
Office Version
  1. 2019
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

BruceyBonu$

Board Regular
Joined
Mar 6, 2006
Messages
74
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,644
Office Version
  1. 365
Platform
  1. Windows
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>
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,994
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,644
Office Version
  1. 365
Platform
  1. Windows
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.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,994
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,644
Office Version
  1. 365
Platform
  1. Windows
Are there other changeable cells in this sheet besides what is in column B?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,994
Office Version
  1. 2019
Platform
  1. Windows
No, there are only changeable cells in column B

Howard
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,644
Office Version
  1. 365
Platform
  1. Windows
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>
 

Forum statistics

Threads
1,141,587
Messages
5,707,252
Members
421,498
Latest member
matinebi

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