Excel: Find last row - check ALL columns? 1

[VBAjedi]

Ok - I know a bunch of ways to find the last used cell in a column, but I want to find the last used row (checking ALL columns - or at least the first 20 columns - for data).

I tried using:

LastSDRow = Range("A63000:Z63000&quot.End(xlUp).Row

but it only checks column "A". This works:

a = ShSD.UsedRange.Rows.Count
b = ShSD.UsedRange.Columns.Count
LastSDRow = ShSD.Cells.SpecialCells(xlCellTypeLastCell).Row

but it's too bulky. I want a one-liner! LOL



VBAjedi

 

[SkipVought]

Hi,

Function LastRow() as Long
With ActiveSheet.UsedRange
   LastRow = .Row+ .Rows.Count - 1
End With
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

Hi
Another method

r = Cells.Find(what:="*", after:=Range("A1"), _
    searchorder:=xlByRows, searchdirection:=xlPrevious).Row

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[VBAjedi]

Loomah,

Thanks! That's just what I wanted. Nice bit of code!
VBAjedi

 

[krinpit]

The code works fine for all of the above, except that it always finds the very last row in the worksheet.

eg: i might have only 100 rows populated, but when I try to find the lastRow, it gives me row 1305.

Any tips on stopping it from misjudging the selection area?

Thanks.

 

[Loomah]

Hi krinpit
Which bit of code?

The code Skip posted may well still be restricted by the limitations of UsedRange which will include formatting remnants. It should reset on saving but......

The code I posted is not affected by formats. However if you have a space, ie " ", in a cell this code will find it, in the same way that using .End(xlup/down) would. I would suggest deleting row 1305 and try again.

There are also two FAQs on this subject. Ok one's mine and the code's above but also have a look at xlbo's.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[krinpit]

Loomah: My appologies, you're quite correct. This has speeded up my application process by a few seconds.