Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Clueless
 
Posts: n/a
Default 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.
  #2   Report Post  
Jim Rech
 
Posts: n/a
Default

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.


  #3   Report Post  
Clueless
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting rows based on IF statement simmerdown Excel Discussion (Misc queries) 1 September 9th 05 02:18 PM
Can I format an entire row based on the value of a single cell? LTShelley Excel Discussion (Misc queries) 1 August 11th 05 07:37 PM
Cell Formula reference to cell Based On third Cell Content Gabriel Excel Discussion (Misc queries) 1 February 11th 05 07:36 AM
Cell Formula reference to cell Based On third Cell Content Gabriel Excel Discussion (Misc queries) 0 February 11th 05 06:35 AM
How do I change content of a cell based on date diving1 Excel Worksheet Functions 2 December 19th 04 09:39 PM


All times are GMT +1. The time now is 10:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"