How to hide a row based on a cell's value

[wattzz2000]

I have a large sheet that has UPC codes and I want to be able to have the sheet automatically eliminate the cells that have a 0 value.
All the cells contain formulas generating data from numerous tabs.
I am very green as far as VBA is concerned, so step by step directions would be necessary.

The data is contained in cells A33:F2263 and the column that would have a 0 value is D. D has a formula in it causing the several methods I have tried to be ineffective.

I have read Macros would help, but I have no idea how to set one up or add the button to activate it.

 

[wattzz2000]

Correction I want to eliminate the rows where D has a 0 value.

 

[Peter_SSs]

In a copy of your workbook, try the code from post #6 here, but alter these lines

a = Range("D33", Range("D" & Rows.Count).End(xlUp)).Value


With Range("A33").Resize(UBound(a), nc)

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code suggested into the main right hand pane that opens at step 2 & edit it as described.
4. Close the Visual Basic window.
5. Back in your worksheet, press Alt+F8, select the Del_Zero macro & click 'Run'
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[bhos123]

you can simply filter on D, and select '0', then right click and select 'delete row' or 'hide row'. whichever you prefer.

 

[wattzz2000]

you can simply filter on D, and select '0', then right click and select 'delete row' or 'hide row'. whichever you prefer.

I tried that; however, some of the users aren't that Excel savy, so I was hoping for a more user friendly option. A point and click or F5 kind of option to alleviate me having to do it all the time or hunt for errors.

I thank you for your reply.

 

[wattzz2000]

Works great! Thank you.

Is this a one time operation, or can you run it again if some more lines are activated / added?

 

[Peter_SSs]

Works great! Thank you.

Is this a one time operation, or can you run it again if some more lines are activated / added?

You are welcome. You can run the code as often as you like. It will always check from D33 to the last cell with data in column D.

 

[wattzz2000]

You are welcome. You can run the code as often as you like. It will always check from D33 to the last cell with data in column D.

After it runs the first time and eliminates the lines, if I go back and add more codes, it doesn't add the new codes in. Is there a way to hide the lines in lieu of delete them? This way if the information is modified at a later date the rows will reappear.

 

[Peter_SSs]

... it doesn't add the new codes in.

I don't really understand that as the macro was about deleting rows, not adding them. In any case if you would rather...

Is there a way to hide the lines in lieu of delete them? This way if the information is modified at a later date the rows will reappear.

.. then 'Yes', use a filter as suggested by bhos123.

Assuming a heading in D32, try

Sub Hide_Zero_Rows()
  ActiveSheet.AutoFilterMode = False
  Range("D32", Range("D" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="<>0"
End Sub

 

[wattzz2000]

Peter:
I changed the original code to this and it seems to work well:

On Error Resume Next
.Columns(nc).SpecialCells(xlConstants).EntireRow.Hidden = True
On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

Do you see any flaw in this?