Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows based on IF statement | Excel Discussion (Misc queries) | |||
Can I format an entire row based on the value of a single cell? | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
How do I change content of a cell based on date | Excel Worksheet Functions |