Adding to a Row Range

[VicWestVan]

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

[Tom Ogilvy]

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

[excelent]

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

[Tom Ogilvy]

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

[VicWestVan]

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