# Solved: Excel 2007 Auto Hide/Unhide Rows



## rendi (Aug 12, 2010)

I will eventually want this to work on all worksheets in the workbook as their data may change as the "linked tables" are refreshed. The linked tables are from Access.

What I want to do is look in column Q to see if the result of the calculation in that cell is 0. If so, I want the row hidden. I don't want to hide empty rows, just the one where the calculation =0. If the data in the workbook, i.e. the linked tables are updated, then I want any rows that have changed from 0 to another value to then become unhidden.

I have been searching for VBA code to auto hide/unhide rows in Excel 2007 based on the value in a specific column. I found the following code, but can't seem to get it to work quite right. I changed some of the criteria to fit my worksheet. The problem is that it hides all rows that are empty even though I changed the criteria from "" to 0. Also, this does not unhide rows that may refresh with a value. 

I would really appreciate some guidance. My Access and Excel skills are pretty good, but when it comes to incorporating VBA into the mix, I get a little lost.



Sub HideEmptyRows()
'This macro hides all of the rows which are empty, for printing.
'created by Geoff Faulkner 12-29-2004

'Declare variables
Dim intStartRow
Dim intEndRow
Dim intTargetColumn
Dim intCounter

'set default values
intStartRow = 6
intEndRow = 2000
intTargetColumn = 17

'cycle through each row in the range
For intCounter = intStartRow To intEndRow Step 1
'if the cell contains no value
If Cells(intCounter, intTargetColumn).Value = " " Then
'Select the row
Rows(intCounter).Select
'hide the row
Selection.EntireRow.Hidden = False

If Cells(intCounter, intTargetColumn).Value = 0 Then
'Select the row
Rows(intCounter).Select
'hide the row
Selection.EntireRow.Hidden = True
End If
End If
Next
End Sub


----------



## Keebellah (Mar 27, 2008)

I editted your macro and put it in the attached sample

two buttons to demonstrate, I changed the range from 6 to 12 or something to show.

Just edit to your needs


----------



## rendi (Aug 12, 2010)

Hans,

Thank you so much, this is perfect!!!  

The only change I made other than the end row # was using a keyboard shortcut instead of the button and I needed to put a value in the cells that were empty in column Q that has a title in column A. One last question, however, with future worksheets in this workbook, do I have to add the module to each worksheet or will this work for any worksheet that happens to be active?

Kendra


----------



## Keebellah (Mar 27, 2008)

The module is only active in the sheet it's in,
If you need it to be valid for any open worksheet you will have to put it the the Persona.xls file which is created when you store macro's in there.
Notice that when you record a macro it asks where you want to store it, if you say teh Personal macro book it will place it there, it's a hidden sheet (someting like the normal.dot in word) but once you have one macro in it, you can access the vba project via de vba editor and just copy any code you want to the personal.xls vba project sheet, then it's there for every sheet.

I hope I have been able to explain it in an understanding way.

One hint, if you do this make sure you add some test so that the macro won't be excecuted by accident on any sheet.


----------



## rendi (Aug 12, 2010)

Got it. Thanks again for all your help!


----------

