I need to find the row number of the last cell in column A:A. Column A:A
contains many gaps, and the data is a mixture of dates and text.

For Example: The formula should return 10 for the following data.
1/21/2006
David

Sam
Paul

2/5/2008
Fred

David

Maybe

Lastrow = Range("A65536").End(xlUp).Row

Mike

"Sloth" wrote:

I need to find the row number of the last cell in column A:A. Column A:A
contains many gaps, and the data is a mixture of dates and text.

For Example: The formula should return 10 for the following data.
1/21/2006
David

Sam
Paul

2/5/2008
Fred

David

hi
can you get to it from the bottom.
MsgBox Cells(Rows.Count, "a").End(xlUp).Row

regards
FSt1

"Sloth" wrote:

I need to find the row number of the last cell in column A:A. Column A:A
contains many gaps, and the data is a mixture of dates and text.

For Example: The formula should return 10 for the following data.
1/21/2006
David

Sam
Paul

2/5/2008
Fred

David

Here's a little macro to do it.

Sub LastRowColumnA()
Dim lastrow
Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
MsgBox "The last row in column A is Row " & lastrow
End Sub


Vaya con Dios,
Chuck, CABGx3



"Sloth" wrote:

I need to find the row number of the last cell in column A:A. Column A:A
contains many gaps, and the data is a mixture of dates and text.

For Example: The formula should return 10 for the following data.
1/21/2006
David

Sam
Paul

2/5/2008
Fred

David

I may have misunderstood and assumed VB so try this if you want a formula.
It's an array so enter with CTRL+Shift+Enter

=ROW(INDIRECT("A"&MAX(IF(NOT(ISBLANK(A1:A65535)),R OW(1:65535)))))

Mike

"Sloth" wrote:

I need to find the row number of the last cell in column A:A. Column A:A
contains many gaps, and the data is a mixture of dates and text.

For Example: The formula should return 10 for the following data.
1/21/2006
David

Sam
Paul

2/5/2008
Fred

David

Try this *array* formula:

=MAX(IF(1-ISBLANK(A1:A1000),ROW(A1:A1000)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.


You *can't* use total column references (A:A) in array formulas unless
you're using XL07!


HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Sloth" wrote in message
...
I need to find the row number of the last cell in column A:A. Column A:A
contains many gaps, and the data is a mixture of dates and text.

For Example: The formula should return 10 for the following data.
1/21/2006
David

Sam
Paul

2/5/2008
Fred

David

Well I should have asked what I really wanted to begin with. I want to
delete the bottom 10 rows of my sheet with a macro. I thought I could figure
it out myself, but I can't get the syntax right.

"Mike H" wrote:

Maybe

Lastrow = Range("A65536").End(xlUp).Row

Mike

Often a good idea to ask what you want <g try this

Sub substance()
Range("A65536").End(xlUp).Offset(-9, 0).Select
Set destrange = ActiveCell.Resize(10, 1)
destrange.ClearContents
End Sub
Mike

"Sloth" wrote:

Well I should have asked what I really wanted to begin with. I want to
delete the bottom 10 rows of my sheet with a macro. I thought I could figure
it out myself, but I can't get the syntax right.

"Mike H" wrote:

Maybe

Lastrow = Range("A65536").End(xlUp).Row

Mike

I finally got it. Thanks everyone. Mike your macro deletes the last 10
cells in column A, not the rows. Thanks for helping though (I used your
first suggestion)!!! The following is the code I used.

lastrow = Range("A65536").End(xlUp).Row
Rows(lastrow - 10 & ":" & lastrow).Delete


"Mike H" wrote:

Often a good idea to ask what you want <g try this

Sub substance()
Range("A65536").End(xlUp).Offset(-9, 0).Select
Set destrange = ActiveCell.Resize(10, 1)
destrange.ClearContents
End Sub
Mike