Last non-blank cell in a column
 

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?

Last non-blank cell in a column
 

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?

}

Last non-blank cell in a column
 

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?