Excel help to find last row

[saran26]

Hi All,

I need to find the last row in an excel sheet. I have a worksheet with many sheets in it.

When i try to get the value of last row using the following formula, even after specifying the sheet as sheet21 , it is returning the last row in the main sheet that is sheet1.

Any thing I am missing ?

LastRow = Sheet21.UsedRange.Rows.Count

Please let me know.

Thanks
Saran

[JBeaucaire]

Try this approach instead...UsedRange is notorious for giving false answers even if you do get the macro to read the correct sheet. In this sample, I'm spotting the "last row" based on column B. Change that to the column you KNOW will always have a value in the last row.

Please Login or Register  to view this content.

Use the actual sheet name in place of "Week 5".

[daniel_gudman]

The traditional "trick" to grab the last row is with the MATCH function...

Please Login or Register  to view this content.

where "9E308" is a value much, much bigger than anything you've got in the column, and a:a is the column you're looking in.

I'm a big fan of avoiding macros and VBA when there's a simplier solution... of course, if this is a single error in a larger project, this might not be very helpful.

[shg]

=MATCH(9E308, a:a)

You can't use 9E308 in a formula, daniel; the largest value is a tad less than 1E308 (i.e., 9.99999999999999E+307)