Naming range where the no of rows indeterminate
 

Could somebody please explain how, within a macro, to define a rang
which applies to -n- rows of data.
Each time the spreadsheet is created the number of lines contained wil
change, the 1st blank line will be blank.

Many thank

--
Feedsma
-----------------------------------------------------------------------
Feedsman's Profile: http://www.excelforum.com/member.php...fo&userid=3437
View this thread: http://www.excelforum.com/showthread.php?threadid=54156

Naming range where the no of rows indeterminate
 

By "the first blank line will be blank": do you mean that the first blank
line signifies the end of the range?

There are a few techniques you can use to find the next blank row down (or
blank column across) from any cell. First, there is the .End method:
LastNonBlankCell = Range("A1").End(xlDown)

Then, there is the .CurrentRegion property:
With Range("A1")
LastNonBlankRow = .CurrentRegion.Rows(.CurrentRegion.Rows.Count)
End With

Or, you can iterate downward:
Set ThisCell = Range("A1")
While ThisCell.Value<""
Set ThisCell = ThisCell.Offset(1,0)
Wend
LastCellDown = ThisCell
--
- K Dales


"Feedsman" wrote:


Could somebody please explain how, within a macro, to define a range
which applies to -n- rows of data.
Each time the spreadsheet is created the number of lines contained will
change, the 1st blank line will be blank.

Many thanks


--
Feedsman
------------------------------------------------------------------------
Feedsman's Profile: http://www.excelforum.com/member.php...o&userid=34377
View this thread: http://www.excelforum.com/showthread...hreadid=541561