I am using a range consisting of part of a single column. How can I
find the first and last cells within the range that contain data?
These cells will not necessarily be the first and last cells in the
range though.

{=SMALL(ROW(A1:A1000)*NOT(ISBLANK(A1:A1000)),COUNT BLANK(A1:A1000)+1)}
gives the row number of the first nonblank cell in range a1:a1000
{=MAX(ROW(A1:A1000)*NOT(ISBLANK(A1:A1000)))}
gives the row number of the last nonblank cell in range a1:a1000

Both formulas are array formulas and are entered with CTRL+SHIFT+ENTER.


wrote:

I am using a range consisting of part of a single column. How can I
find the first and last cells within the range that contain data?
These cells will not necessarily be the first and last cells in the
range though.

To get the last row used in Column a

LastRow = Range("A" & Rows.Count).End(xlUp).Row


To get last row used in spreadsheet regardless of which column has th
entry

GetBottomRow = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row



To get 1st row with data use
Note this will not work correctly if rows 1 and 1 are not blank

FirstRow = Range("A1").End(xlDown).Ro

--
Message posted from http://www.ExcelForum.com