Locate last cell in column

[Sloth]

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

 

[Mike H]

Maybe

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

Mike

 

[FSt1]

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

regards
FSt1

 

[CLR]

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

 

[Mike H]

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)),ROW(1:65535)))))

Mike

 

[RagDyeR]

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!
=====================================================

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

 

[Sloth]

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]

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]

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