First and Last cells with data in a range

[PeterS]

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.

 

[mudraker]

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

 

[JWolf]

{=SMALL(ROW(A1:A1000)*NOT(ISBLANK(A1:A1000)),COUNTBLANK(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.