Last Row

[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.

 

[Bob Phillips]

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)

 

[Frank Kabel]

Hi
in VBA:
Dim rng as range
set rng = Cells(1,1).End(xldown)

 

[Ron de Bruin]

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