Last Row

  • Thread starter Thread starter Birdy
  • Start date Start date
B

Birdy

How can i find the last row on a worksheet ?

on a worksheet the first row is row7 and i also want to find last row in a
formula.
 
Birdy,

I assume you mean the last row with data. This code finds the last row with
data in column A, and caters for embedded blank rows.

cLastRow = Worksheets("Sheet1").Cels(Rows.Count,"A").End(xlUp).Row

If you want the first free row, just add 1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Birdy

If you can't check one column like in Bob's example then
for the last row with data on your worksheet you can use this

Sub test()
MsgBox LastRow(ActiveSheet)
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
Back
Top