Hiding Columns with Zero Values

[howard]

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

 

[BruceyBonu$]

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

 

[BruceyBonu$]

sorry I read that wrong, my apologies.
take out the ' Selection.EntireRow.Delete

 

[Peter_SSs]

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]

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]

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]

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]

Are there other changeable cells in this sheet besides what is in column B?

 

[howard]

No, there are only changeable cells in column B

Howard

 

[Peter_SSs]

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>