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.

 

[CWatts]

Try this. Since you said it was going to be used in multiple capacities, I didn't lock it to Columsn B and Q.

Just select any range of cells you want to check. It will unhide all cells by default and then will go row by row and if all the cells in the row are equal to 0 it will hide it..

Sub SelectionHide()
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
Dim Status As Integer
Dim rng As Range
Set rng = Selection

rng.Rows.Hidden = False
 
For Each r In rng.Rows
    Status = 0
    For Each c In r.Cells
        If c.Value = 0 Then Status = Status + 1
    Next c
    If Status = rng.Columns.count Then r.Hidden = True
Next r
 
Application.Calculation = xlManual
Application.ScreenUpdating = True
End Sub

 

[CWatts]

Typo in last line:

Application.Calculation = xlManual

should read

Application.Calculation = xlAutomatic

 

[LearnExcel1990]

Hello,


Okay I tried that code and it doesnt seem to be outputting the result I was hoping for. I think the problem could be that column A has descriptions for each line item?

I want the code to look at columns B through Q, check to see if all cells in each of those columns have zeros. If they have zeros then hide that line and if those cells in each of those columns are blank then ignore and do not hide.

I appreciate you help so much.
Thanks.

 

[LearnExcel1990]

Hi CWatts,

Would you still be able to help me out with this one?

 

[CWatts]

Sorry I missed your last reply, must have accidentally deleted the email notification. Since you said things would change, the macro was written to only look in the cells selected, so in your case, only highligh cells in columsn B through Q, but not COlumn A. That will keep your headers in place and will only hide them if there is data in B:Q.

It can be set to only B:Q, but it would lose the versitility you expressed you wanted on other sheets.

 

[kchan201]

Cwatts, your code has helped me greatly. If you're still around could you help me with an additional variable?

I want to have the script hide the row only if 2 cells in a row are 0. For example in the range G7:200 & E7:200. If both cells in G & 7 equals 0 then hide. Is this possible?

Thank you!

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

 

[CWatts]

Untested, but just try this.

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

 

[kchan201]

AMAZING! Code worked perfectly. Cwatts I envy your VBA skills.

Untested, but just try this.

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

 

[Clairebryan]

Hi there,

I NEED HELP: I a looking for a code which will hide a row if cells C, D & E all contain Zero.

Thank much

 

[nathanr33]

Hello, just came across this older thread.
this is exactly what im trying to do with some sheets.

the only problem is i cannot get the code to work
i have changed the code to column D for my use

the problem is it is not working. possibly because the value in in column D is a link to another sheet or another document so the cell in D may show 0 but is linked to another document or another sheet. is there still a way for this code to see this cell as the 0 value even though it is a link?


Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual

Rows("7:200").Hidden = False

For Each c In Range("D4:d200")
If c.Value = 0 And c.Value <> "" Then Rows(c.Row).Hidden = True
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub