Last non-blank cell in a column

  • Thread starter Thread starter Syd
  • Start date Start date
S

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?
 
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
 
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)))
 
Back
Top