Hide rows and columns based on cell values

[Jagadeeshbs]

Hi all

I have values in the range B3 :DV54, in this range some of the cells values are 0. Now I want to hide the row & columns if the cell values are 0.

How can I do this ..? Please help me

Pls find the example:

MI PY GC LT

0 0 0 0

0 0 0 0

0 0 0 0

0 0 882 0

0 0 0 0

 

[vijaySharma]

Jagadeesh,


In this you have 51 rows and 125 columns of data....

what are the chances of the entire column not having any data point at all (having only zero).


and the same applies to the Rows as well...


~VijaySharma

 

[Jagadeeshbs]

VijaySharma


In the entire ranges some of the cells values are more then 0.

But I want to hide all the rows and columns whose values are 0.

If the cell values are more than 0 it should show the entire row’s or columns or both.


Thanks

 

[vijaySharma]

Jagadeesh,


You need to add one helper row and column with your data.


In cell B55 put =sum(B3:B54) and copy this till DV55

In cell DW3 put =sum(B3:DV3) and copy down till DV54


post this... you can use the below code to get the output...

Sub ShowHide()


    'hiding the rows


    Range("DW3").Select

Do While ActiveCell.Value <> ""

If ActiveCell.Value = 0 Then

ActiveCell.EntireRow.Hidden = True

End If

ActiveCell.Offset(1, 0).Select

Loop


    'hiding the columns

Range("B54").Select

Do While ActiveCell.Value <> ""

If ActiveCell.Value = 0 Then

ActiveCell.EntireColumn.Hidden = True

End If

ActiveCell.Offset(0, 1).Select

Loop

End Sub

~VijaySharma

 

[Jagadeeshbs]

Vijay

Thank you very much....it works very well

Have a nice weekend

 

[jujubz826]

Hi, I have a similar question, except I am curious as to how I would go about it if I only want the rows to be hidden if the sum of multiple columns equals zero.

 

[jujubz826]

I was able to get what I needed, but I have to do a seperate code for each section on because the data I want to hide/display is seperated by product groups and under each group there is other information that I want to always display. Is there a way to have one code that would work on all the worksheets and other workbooks? I have over 30 workbooks and each have at least 3 worksheets and 10 product groups. The code I'm using right now for two product groups is:


Sub ShowHideRows()


'hiding the rows


Range("AF8").Select

Do While ActiveCell.Value <> ""

If ActiveCell.Value = 0 Then

ActiveCell.EntireRow.Hidden = True

End If

ActiveCell.Offset(1, 0).Select

Loop


Range("AF39").Select

Do While ActiveCell.Value <> ""

If ActiveCell.Value = 0 Then

ActiveCell.EntireRow.Hidden = True

End If

ActiveCell.Offset(1, 0).Select

Loop


End Sub


And how can I have it run the macro automatically when I open the file?