Auto Hide Rows if any of the cells in Column G are 0

[LearnExcel1990]

Hello everyone,
This is my first time using these types of forums. I tried to do a general search on my issue and tried to copy and paste various written VBA language and tried to fudge it but it wasnt working for me. What I am trying to do is to hide any rows which contain a zero value in column G between rows lets say 7 and 200 in one of the sheets

How does VBA work, do I need to run the lines of code everytime I want to have somethign hidden? or does it automatically run in the background.

Thanks for your help guys.

 

[RSXchin]

you don't HAVE to use vba for this. you could just use autofilter. but if you prefer to use vba anyway... give us a few minutes.

(Welcome to the forums)

 

[LearnExcel1990]

The reason I think I need VBA is that I think it would look cleaner for Management if I use VBA. It doesnt have those extra filter drop downs. I know Im picky but it has to be pretty

 

[LearnExcel1990]

Im sorry bo bother you again but did anyone look at this Yet?

 

[CWatts]

Try this:

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
For Each c In Range("G7:G200")
    If c.Value = 0 Then Rows(c.Row).Hidden = True
Next
 
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[LearnExcel1990]

Works Great! But what its also doing is some lines in my spreadsheet have title headers and there is nothing in column G (no zeros) and it is hiding those as well. Is there a way to ignore blank cells?

 

[CWatts]

Just added another test to the IF statement.

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
For Each c In Range("G7:G200")
    If c.Value = 0 And c.Value <> "" Then Rows(c.Row).Hidden = True
Next
 
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[LearnExcel1990]

That is exactly what I need! This shaves a tonne of time off my work. You are the best! I need to learn VB.

If I can ask you for another scenario.
Ii want to do a similar thing on another one of my tabs except that I want the rows hidden only if all of the cells in each rown starting from column B through Q have have a 0 value. Some of these cells have formulas in them. Not sure if that makes a difference. But if all of those have a 0 value within that column range than I would like Excel to hide that row.

I dont know if I am being clear or if I am giving you enough information.
Is this possible?

 

[CWatts]

Yes it is possible. Is it still rows 7 to 200? And when you say zero, do you mean actually 0, or do you need to account for blanks as well like with the previous function?

Also, I modified the previous code to help a little bit more by automatically unhiding all the cells first, and then rehiding them based on the criteria.

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
Rows("7:200").Hidden = False
 
For Each c In Range("G7:G200")
    If c.Value = 0 And c.Value <> "" Then Rows(c.Row).Hidden = True
Next
 
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[LearnExcel1990]

The rows will change as I will use this for other instances. When I say zeros I mean that there will be also blank rows in columns B through Q since there are headers that seperate these blocks of data.

I hope that makes sense.