Hide/Delete entire rows based in the content of one cell
 

Hello all. I have a spreadsheet that is over 500 rows long. As it is I have
no use for all of the rows at the same time and have to keep hiding and
showing them as need arises. Is there a macro to hide chunks of it based on
the value of one cell of the row? In other words, en each row I will have a
formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to
hide the row or not. I tried case.select but it takes a LONG time and I would
have to write a piece of code for every line. FYI, the rows that need hiding
will be in sequence, in other words, from 100 to 500, or from 80 to 500,
depends on where the last text was entered. Thanks in advance.

Use a slightly different formula and it becomes a lot easier:

=IF($A$1=1,TRUE,"")

So say these formulas are in the range C1:C20. This macro will first show
all rows in that range and then hide those whose formula returns TRUE.

Sub HideRows()
Application.ScreenUpdating = False
With Range("C1:C20")
.Rows.Hidden = False
.SpecialCells(xlCellTypeFormulas, xlLogical).Rows.Hidden = True
End With
End Sub

--
Jim
"Clueless" wrote in message
...
| Hello all. I have a spreadsheet that is over 500 rows long. As it is I
have
| no use for all of the rows at the same time and have to keep hiding and
| showing them as need arises. Is there a macro to hide chunks of it based
on
| the value of one cell of the row? In other words, en each row I will have
a
| formula like =if(a1=0,"HIDE","") and this value will tell the macro wether
to
| hide the row or not. I tried case.select but it takes a LONG time and I
would
| have to write a piece of code for every line. FYI, the rows that need
hiding
| will be in sequence, in other words, from 100 to 500, or from 80 to 500,
| depends on where the last text was entered. Thanks in advance.

Jim,

You have no idea how much help your macro was. I did a slight modification
and the macro ended up looking like this:

Sub REPORTALOTE()
'
' REPORTALOTE Macro
' Macro grabada el 03-10-2005 por
'

'
Sheets("Lista").Select
Sheets("Lista").Copy
Application.ScreenUpdating = False
With Range("M1:M522")
.Rows.Hidden = False
.SpecialCells(xlCellTypeFormulas, xlLogical).EntireRow.Delete
End With
Cells.Select
Range("A52").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("J:M").Select
Range("J52").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("E58").Select
Windows("PA QUE PRACTIQUE JHONNY. NO TOCAR.xls").Activate
Sheets("Data").Select
Range("G1:H1").Select
End Sub

Again, you helped me to no end so I thought to post the final macro in case
someone else needed it. Thanks a lot.

Jose E. Solano

"Jim Rech" wrote:

Use a slightly different formula and it becomes a lot easier:

=IF($A$1=1,TRUE,"")

So say these formulas are in the range C1:C20. This macro will first show
all rows in that range and then hide those whose formula returns TRUE.

Sub HideRows()
Application.ScreenUpdating = False
With Range("C1:C20")
.Rows.Hidden = False
.SpecialCells(xlCellTypeFormulas, xlLogical).Rows.Hidden = True
End With
End Sub

--
Jim
"Clueless" wrote in message
...
| Hello all. I have a spreadsheet that is over 500 rows long. As it is I
have
| no use for all of the rows at the same time and have to keep hiding and
| showing them as need arises. Is there a macro to hide chunks of it based
on
| the value of one cell of the row? In other words, en each row I will have
a
| formula like =if(a1=0,"HIDE","") and this value will tell the macro wether
to
| hide the row or not. I tried case.select but it takes a LONG time and I
would
| have to write a piece of code for every line. FYI, the rows that need
hiding
| will be in sequence, in other words, from 100 to 500, or from 80 to 500,
| depends on where the last text was entered. Thanks in advance.