I need help hiding and unhidiing rows Macro

January 31, 2011 at 17:54:06
Specs: Windows Vista
I need to hide a row when a cell in that row is 0 or blank and show the row when the cell is greater than 0 and the cell is formated from another doc so it changes on its own. Excel 2007

eg. If G8 is 3 then row 8 will show if G8 goes to 0 or blank then the row will hide And this is seperate for all rows from 8 to 53

Please Help Thanks


See More: I need help hiding and unhidiing rows Macro

Report •

#1
February 1, 2011 at 06:41:49
In the spirit of giving you what you ask for . . .
Sub HideRows()
  Dim row As Range
  For Each row In ActiveSheet.Range("8:53").Rows
    row.Hidden = WorksheetFunction.CountBlank(row) _
        Or WorksheetFunction.CountIf(row, 0)
  Next 'row
End Sub

How To Ask Questions The Smart Way


Report •

#2
February 1, 2011 at 06:43:31
re: "I need to hide a row when a cell in that row is 0 or blank "

When you say "a cell in that row" do you mean any cell or a cell in a specific column?

re: "the cell is the cell is formated from another doc "

When you say "the cell is formated from another doc" do you mean calculated from data in another worksheet?

Please supply some more details as to how the values get into the cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 1, 2011 at 07:13:04
A specific cell in a column eg when G8 is 0 I wants to hide row 8 and when its greater than 0 I want the row row show

The value in the cell is from another sheet so it changes on its own and I dont know if this matters but the cell is locked


Report •

Related Solutions

#4
February 1, 2011 at 07:40:20
A specific cell in a column
Any reason you're being so secretive? I'm pretty sure the list of cells isn't confidential, proprietary information. Is it every cell in column G?

How To Ask Questions The Smart Way


Report •

#5
February 1, 2011 at 18:27:51
Yes G.8 to G.53

Report •

#6
February 1, 2011 at 18:49:11
I tried the formula it removed all rows but even if there was a 7 in G8 it removed it

Is there more code to it also if you want the row to show when the cell changes to greater than 0 these G cells will change on there own and the rows need to hide and show whenever a change is made

Can this be done?


Report •

#7
February 1, 2011 at 19:02:11
I will Clarify When cell G8 is greater than 0 the row will show when G8 changes to zero or goes blank the row will hide. That one G cell will control wether the row is showing or hidden. This is repeated seperatly for all rows for 8 to 53 then again from 55 to 63 all controled by what is in that rows G cell. The number in that cell will change and once the row is hidden it needs to show on its own when the cell goes greater than 0.

I dont know if it can work automaticaly like this or not any help would be much appreciated

Thanks


Report •

#8
February 1, 2011 at 21:42:02
I used
Sub HideRows()
Dim cell As Range
For Each cell In Range("G8:G53,G55:G63")
If (cell.Value) < "0" Then
cell.EntireRow.Hidden = True
ElseIf cell.Value > "0" Then
cell.EntireRow.Hidden = False
End If
Next
End Sub

But it doesnt do it automaticaly is there a way that it will constantly monitor those cells and change on its own?


Report •

#9
February 1, 2011 at 21:57:13
Also is when I lock some cells in these rows it doesnt let me hide is there any fix to get around that?

Report •

#10
February 2, 2011 at 06:59:31
Kevin.wilhelm: I tried the formula it removed all rows but even if there was a 7 in G8 it removed it
Of course it did. You asked for, and I quote, "I need to hide a row when a cell in that row is 0 or blank." There are (in Excel 2003) 256 chances per row that a cell will be empty or 0. That's why I called it, "giving you what you asked for."

Kevin.wilhelm: these G cells will change on there [sic] own and the rows need to hide and show whenever a change is made
Without knowing how they change or what changes them (again with the needless vagueness), there really isn't an answer. Maybe you can use Worksheet_Change, maybe you can't.

Kevin.wilhelm: Also is when I lock some cells in these rows it doesnt let me hide is there any fix to get around that?
You must either unlock the sheet, allow row formatting while locked, or lock the sheet with UserInterfaceOnly set to True.

Sub HideRows()
  Dim row As Range
  For Each row In ActiveSheet.Range("G8:G53,G55:G63").Rows
    row.Hidden = WorksheetFunction.CountBlank(row) _
        Or WorksheetFunction.CountIf(row, 0)
  Next 'row
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  HideRows
End Sub

How To Ask Questions The Smart Way


Report •

Ask Question