Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 695
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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
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
Adding a Range Joey041 Excel Worksheet Functions 1 November 21st 06 10:23 AM
range adding Toman[_3_] Excel Programming 3 March 10th 06 01:03 PM
Adding named range gives error "method range of object _Global failed " Gunnar Johansson Excel Programming 3 August 10th 04 01:54 PM
adding reference-to-range control to excel range Nir Sfez Excel Programming 1 March 2nd 04 07:11 PM
sheets.range and adding cells that are out of the range Phillips Excel Programming 1 November 18th 03 10:27 PM


All times are GMT +1. The time now is 10:36 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"