How do I hide cells with "0" value but unhide if value changes?

Question

I have a workbook, Sheet1 looks identical to this this sheet, but this sheet copies the cells from Sheet1. My question is how do I auto hide rows if the cells in column A are "0"? I know there's some magic VBA code to do this, but I have no experience it the arena. Not apposed to using it, but if you have any suggestions, explain it to me as if I were a child. A very, dumb child.

Answer 1

Hi,

try this approach,

select the desired range in column A

(based on the above picture is: A2:A14)

and add autofilter

next,

in list (cell A2) unmark zero (0) and blanks (if exist)

Answer 2

i guess i should have stated that i have three of these tables on one sheet. So, unless I'm totally out of the loop, I don't think i can use the auto filter feature. Each table needs to operate independently.

Answer 3

If the rows to be hidden are always at the end, Conditional Formatting might help.

The idea is that if the font color matches the background (white in this case), the cell content becomes invisible. Though invisible, values will still actually be there, so be careful with formulas that may include them.

Select A3:J3, click on Conditional Formatting, and apply the formula

   =$A3=0

Click "Format" and set Font>Color to white; then close the Conditional Formatting dialog box. Finally, use the Format Painter to apply the Conditional Formatting to the rest of the table.

Answer 4

I would like to hide the rows to condense the tables in a smaller area. Is there a easy way to do that with Macros? One that would hide the rows if the value of call Awhatever is zero, but would unhide it if the value changed? Ive tried other VBA code that would hide the zero value rows, but for the life of me I couldn't figure out how to unhide them.

Answer 5

Hi,

Assuming that you have, on Sheet2, two (three-four,...)  'tables' 

as below

pic-1

table1 in A2:J14

table2 in A19:J31

convert the above ranges to REAL Tables

select each range separately,

 from the ribbon select Insert > Table

result:

pic-2

final result (without zeros)

pic-3

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Since you have real tables on Sheet 2,

if you want to do this automatically

try this....

if you are not familiar with macros

step1

enable macros, take a look this video

https://www.youtube.com/watch?v=vtTa8y6i_rM

step2

Save As, your Workbook with extension .xlsm (macros enabled)

step3

1. press ALT+F11 to open Visual Basic
2. select: Insert > Module and paste the code below (Sub****Update_ListObj - End Sub) on the right 
3. Press ALT+Q to Close Visual Basic

Sub Update_ListObj()

'Oct 01, 2016

Dim ws As Worksheet

Set ws = Sheets("Sheet2") '<< tables on sheet2, change name as needed

For Each obj In ws.ListObjects

obj.Range.AutoFilter field:=1

obj.Range.AutoFilter field:=1, Criteria1:="<>0"

Next

End Sub

step4

To run the macro, press ALT+F8, 

select *'*Update_ListObj' from the list and click the run button.

or

add a button and assign the  vba macro

Note

I assume that *'ignore zeros'*is only for the 1st column

XXXXXXXXXXXXXX

also,

visible ALL rows in Tables

Sub Visible_ALL_Rows()

'Oct 01, 2016

Dim ws As Worksheet

Set ws = Sheets*("Sheet2") '<< tables on sheet2***

For Each obj In ws.ListObjects

obj.Range.AutoFilter field:=1

Next

End Sub