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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi Laura,

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

L0llyp0p

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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

New Member
Joined
Mar 27, 2019
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Laura,

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

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
 

L0llyp0p

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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

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
Top