Last non-blank cell in a column

[Syd]

Is there a function to get the address of the last cell that contains data.
I want to set a range to start at the first cell in a colomn containing data
and end with the last cell that contains data. The last cell containing data
must update with added data?

 

[David McRitchie]

Hi Syd,

Look over these two pages


Working with MS Excel Toolbars, Custom Buttons and Menus
http://www.mvps.org/dmcritchie/excel/toolbars.htm

Fill-Handle, Replication and use of the Mouse
http://www.mvps.org/dmcritchie/excel/fillhand.htm

I expect you meant down from the second cell in the column or
from the active cell in the column, but the following starts at
row 1 like you asked..

Example as an Event macro
http://www.mvps.org/dmcritchie/excel/event.htm
to install right click on the worksheet tab, view code and insert the following
after your "Option Explicit"


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Range(Cells(1, ActiveCell.Column), Cells(Rows.Count, _
ActiveCell.Column).End(xlUp)).Select
End Sub

 

[RagDyer]

This *array* formula should give you the row number of the last cell that
contains data.
Nulls (zero length strings ... "") are excluded.

=MAX(IF(A1:A100<>"",ROW(A1:A100)))