Code to Hide/ Unhide rows using VBA

L0llyp0p

New Member
Joined
Mar 27, 2019
Messages
4
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi Laura,

Before getting into a macro ... why don't you start by using Filter ... :)
 
Upvote 0
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.
 
Upvote 0
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 ...
 
Upvote 0
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
 
Upvote 0
Laura,

There is no way to Partially Hide a Row .... say for the January Column BUT not for the February Column :wink:
 
Upvote 0
For your tests, below is a macro ...

Code:
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
 
Upvote 0
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.
 
Upvote 0
Hi,

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

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

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top