Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a Row Range
I currently have a simple Loop applied for example to rows 1- 10 that Hides
consecutive empty rows... it checks a row (starting from 10), if the row is empty, the row is hidden, then moves to row 9 and repeats, all the way to row 1. Do Until ActiveCell < "" Selection.EntireRow.Hidden = True ActiveCell.Offset(-1, 0).Select Loop Any suggestions on how to change the code to check a row i.e. Row 10, if the row is empty, then add Row 10 to the Row Range, then check Row 9, add 9:10 to the Row Range, then after all checking, apply the HiddenRows command to the final Row Range? Thx, Vic |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a Row Range
Dim rng as Range
Do Until ActiveCell < "" if rng is nothing then set rng = ActiveCell else set rng = union(rng,activecell) end if if ActiveCell.row = 1 then exit do ActiveCell.Offset(-1, 0).Select Loop if not rng is nothing then rng.Entirerow.Hidden = True end if -- Regards, Tom Ogilvy "VicWestVan" wrote: I currently have a simple Loop applied for example to rows 1- 10 that Hides consecutive empty rows... it checks a row (starting from 10), if the row is empty, the row is hidden, then moves to row 9 and repeats, all the way to row 1. Do Until ActiveCell < "" Selection.EntireRow.Hidden = True ActiveCell.Offset(-1, 0).Select Loop Any suggestions on how to change the code to check a row i.e. Row 10, if the row is empty, then add Row 10 to the Row Range, then check Row 9, add 9:10 to the Row Range, then after all checking, apply the HiddenRows command to the final Row Range? Thx, Vic |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a Row Range
try
Sub Hide() Selection.SpecialCells(xlCellTypeBlanks).Select 'Selection.EntireRow.Hidden = True MsgBox ("") & Selection.Address End Sub just run or select range and then run "Tom Ogilvy" skrev: Dim rng as Range Do Until ActiveCell < "" if rng is nothing then set rng = ActiveCell else set rng = union(rng,activecell) end if if ActiveCell.row = 1 then exit do ActiveCell.Offset(-1, 0).Select Loop if not rng is nothing then rng.Entirerow.Hidden = True end if -- Regards, Tom Ogilvy "VicWestVan" wrote: I currently have a simple Loop applied for example to rows 1- 10 that Hides consecutive empty rows... it checks a row (starting from 10), if the row is empty, the row is hidden, then moves to row 9 and repeats, all the way to row 1. Do Until ActiveCell < "" Selection.EntireRow.Hidden = True ActiveCell.Offset(-1, 0).Select Loop Any suggestions on how to change the code to check a row i.e. Row 10, if the row is empty, then add Row 10 to the Row Range, then check Row 9, add 9:10 to the Row Range, then after all checking, apply the HiddenRows command to the final Row Range? Thx, Vic |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a Row Range
This is certainly more efficient, but
if the range contained formulas such as =if(condition,"",value) or if they previously contained such a formula and the OP did edit copy followed by edit=Paste special, values or if the filled cells were not contiguous from A1 then this approach would not operate as the OP's original code. -- Regards, Tom Ogilvy "excelent" wrote: try Sub Hide() Selection.SpecialCells(xlCellTypeBlanks).Select 'Selection.EntireRow.Hidden = True MsgBox ("") & Selection.Address End Sub just run or select range and then run "Tom Ogilvy" skrev: Dim rng as Range Do Until ActiveCell < "" if rng is nothing then set rng = ActiveCell else set rng = union(rng,activecell) end if if ActiveCell.row = 1 then exit do ActiveCell.Offset(-1, 0).Select Loop if not rng is nothing then rng.Entirerow.Hidden = True end if -- Regards, Tom Ogilvy "VicWestVan" wrote: I currently have a simple Loop applied for example to rows 1- 10 that Hides consecutive empty rows... it checks a row (starting from 10), if the row is empty, the row is hidden, then moves to row 9 and repeats, all the way to row 1. Do Until ActiveCell < "" Selection.EntireRow.Hidden = True ActiveCell.Offset(-1, 0).Select Loop Any suggestions on how to change the code to check a row i.e. Row 10, if the row is empty, then add Row 10 to the Row Range, then check Row 9, add 9:10 to the Row Range, then after all checking, apply the HiddenRows command to the final Row Range? Thx, Vic |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a Row Range
Tom... you're the man... thx very much... as you noted later, this works best
in a more general case. Thx again, Vic West Vancouver "Tom Ogilvy" wrote: Dim rng as Range Do Until ActiveCell < "" if rng is nothing then set rng = ActiveCell else set rng = union(rng,activecell) end if if ActiveCell.row = 1 then exit do ActiveCell.Offset(-1, 0).Select Loop if not rng is nothing then rng.Entirerow.Hidden = True end if -- Regards, Tom Ogilvy "VicWestVan" wrote: I currently have a simple Loop applied for example to rows 1- 10 that Hides consecutive empty rows... it checks a row (starting from 10), if the row is empty, the row is hidden, then moves to row 9 and repeats, all the way to row 1. Do Until ActiveCell < "" Selection.EntireRow.Hidden = True ActiveCell.Offset(-1, 0).Select Loop Any suggestions on how to change the code to check a row i.e. Row 10, if the row is empty, then add Row 10 to the Row Range, then check Row 9, add 9:10 to the Row Range, then after all checking, apply the HiddenRows command to the final Row Range? Thx, Vic |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a Range | Excel Worksheet Functions | |||
range adding | Excel Programming | |||
Adding named range gives error "method range of object _Global failed " | Excel Programming | |||
adding reference-to-range control to excel range | Excel Programming | |||
sheets.range and adding cells that are out of the range | Excel Programming |