what should be the best correct option to use SpecialCells(xlCellTypeLastCell) or Find * or rows.count?

Flora Edwards
Flora Edwards used Ask the Experts™
I am new with in this VBA world.

still learning.

i am confused a bit with the different methods used to find the last row or last column of a specific sheet.

which one should i use?  what is the pros and cons of each of the below methods?
which one of them is more robust so that it will not miss anything?

'Using Find Function
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Open in new window

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

Open in new window

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
There are several discussions on this on a few of the common Excel forum sites.  One that I had bookmarked a while back when looking in to this same topic is below.  I won't rephrase it here, it's better read at the source.  May or may not be what you wanted, maybe more info than desired, but the bottom line is the different approaches have different pros and cons that can make different approaches better depending on circumstances.

Flora EdwardsMedicine


thanks very much.

in my case, then Range.Find will serve me well

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial