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

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

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

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

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