Obtain Row No of xlEnd
 

Excel 2000

I need proper syntax for a statement like:

While ActiveCell.Row < xlEnd.Row

How do I obtain the Row number of the end of the active work area in a
worksheet? The same cell as the cell selected by Ctrl-End.

TIA

Fred Holmes

Obtain Row No of xlEnd
 

Dim LastRow as long
with worksheets("somesheetnamehere")
lastrow = .cells.specialcells(xlcelltypelastcell).row
end with

But excel remembers too much. It'll keep track of that last used cell even if
I've cleared it.

I like to pick out a column that always has data in it if that row is used.

Dim LastRow as long
with worksheets("somesheetnamehere")
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

I used column A in this sample.

Fred Holmes wrote:

Excel 2000

I need proper syntax for a statement like:

While ActiveCell.Row < xlEnd.Row

How do I obtain the Row number of the end of the active work area in a
worksheet? The same cell as the cell selected by Ctrl-End.

TIA

Fred Holmes

--

Dave Peterson

Obtain Row No of xlEnd
 

Figured it out. No response needed. Thanks for all your help.

Fred Holmes

On Thu, 11 Jun 2009 19:11:39 -0400, Fred Holmes wrote:

Good stuff, but is there an Object that is the currently active
worksheet, so that I don't have to know the explicit name of the
current workbook (which would break if the file were renamed)?

ActiveWorksheet.

does not seem to work.

Sub A1_Test()
MsgBox AciveWorksheet.Cells.SpecialCells(xlCellTypeLastCe ll).Row
End Sub

bombs.

And I presume that it would bomb with your code for the last cell in
Column A.

On Thu, 11 Jun 2009 17:54:26 -0500, Dave Peterson
wrote:

Dim LastRow as long
with worksheets("somesheetnamehere")
lastrow = .cells.specialcells(xlcelltypelastcell).row
end with

But excel remembers too much. It'll keep track of that last used cell even if
I've cleared it.

I like to pick out a column that always has data in it if that row is used.

Dim LastRow as long
with worksheets("somesheetnamehere")
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

I used column A in this sample.

Fred Holmes wrote:

Excel 2000

I need proper syntax for a statement like:

While ActiveCell.Row < xlEnd.Row

How do I obtain the Row number of the end of the active work area in a
worksheet? The same cell as the cell selected by Ctrl-End.

TIA

Fred Holmes

--

Dave Peterson