How to find first and last cell in a Sheet with an entry

  • Thread starter Thread starter Hölzl Otto
  • Start date Start date
H

Hölzl Otto

Hallo, friends!
Is there a short way to find the first (last) row(column) that contains an
entry?
Please give me a hint!
Thanks
Otto
 
Otto,

Last row

Range("A1").End(xlDown).Row

If there might be empty cells in that range, then use

Cells(Rows.Count,"A").End(xlUp).Row

Similarly for columns

Range("A1").End(xlToRight).Column

or

Cells(1,Columns.Count).End(xlToLeft).Column

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
dim lastcell as range
set lastcell = Range("A65536").end(xlup)

msgbox "Last row is " & lastcell.row
msgbox "Last column is " & lastcell.column

also a worksheet's UsedRange returns the area 'used' (!)
BUT you alos need to get the Ysed range's position.
So a ysedrange begins in row UsedRange.Row and fills UsedRange.Rows.Count
so adding these together gives the last row use din a sheet...

With ActiveSheet
lastrow = .UsedRange.Row + .UsedRange.Rows.Count - 1
End With


Patrick Molloy
Microsoft Excel MVP
 
Back
Top