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?

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

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Syd" wrote in message ...
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?

}

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)))

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Syd" wrote in message
...
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?