Code to Hide/ Unhide rows using VBA

[L0llyp0p]

Hello,

I have been searching lots of forums to see if I can find a solution to my query but not been very successful.

I am looking to build a VBA into my worksheet to hide rows across numerous columns that show zero values. I would also like to have the ability to unhide these rows when new information needs inserting. I am not very experienced in building macro's/ VBA's. I have managed to build a VBA that hides rows successfully but when I tried to build in a unhide function, it didn't work.

I have used a command button because I am building this file that is shared and updated by numerous users so wanted a command button to help prompt them to use this facility.

I unfortunately cannot share the file due to sensitive information but the information I can share is there are around 2000 rows, spanning across columns C to AK. Starting with column C, there is a formula to demonstrate a word (product), column D then has a formula to show the value so every other column will have a value that is zero or > and this is the columns that I would like the macro to identify.

The columns are also spread by monthly periods, so for January, column C is product and column D is value for January and this format follows on across the months. One month there might be a value above zero and then the next month there is a value that is zero, I would like the macros to show rows that have a value above zero despite other periods not having a value, I am simply looking for a macro that hides rows and columns that both have zero values.

Sorry for the wordy message!! Is this something that can be achievable?

Many thanks in advance.

Laura

 

[James006]

Hi Laura,

Before getting into a macro ... why don't you start by using Filter ...

 

[L0llyp0p]

Hi James,

Thank you for asking the obvious, but if I use a filter it won't show all the months where there is a value.

 

[James006]

Hi James,

Thank you for asking the obvious, but if I use a filter it won't show all the months where there is a value.

Not sure to understand your comment ...

If in a given cell, there is a 0 you need to hide .... the whole Row will be hidden ...

 

[L0llyp0p]

Hi, if I filter on say the column for January to remove all rows with a zero then it hides rows in February where there is a value, I am trying to demonstrate a trend so want to show the values on a monthly basis. Sorry if I am not making myself very clear but I have tried using a filter & it doesn’t work! Many thanks Laura

 

[James006]

Laura,

There is no way to Partially Hide a Row .... say for the January Column BUT not for the February Column :wink:

 

[James006]

For your tests, below is a macro ...

Sub HideZeroRows()
' In Sheet1 - In Column A - Hide Rows for which the A cell contains 0
  With Sheet1
   If .AutoFilterMode = False Then .Range("A1").AutoFilter
          .Range("A1").AutoFilter Field:=1, Criteria1:="<>" & "0"
  End With
End Sub

 

[L0llyp0p]

Hi James, thank you for the code but it has not worked, nothing seems to happen and I have tried it using different columns letters to be more specific to my sheet.

 

[James006]

Hi,

Do not know how familiar you are with Excel ...

Could you quickly describe how your sheet is structured ...?