Hiding Columns with Zero Values

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,622
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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
46,153
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,622
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
46,153
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,622
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
46,153
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,622
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
46,153
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,292
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top