diff between used range row and lastrow

[Guest]

Hi ,

Can someone advise me the difference between if i use
activeworkbook.usedrange.row vs if i use lastrow=row.count.xl.up? Which
should i use if i want to delete those blank rows that are copy from another
wksheet via macro so that the last row will be the one with the data?

thanks,

 

[ward376]

I've gotten a lot of mileage and no errors using find in functions to
identify last rows/columns in many different situations.

Check out http://www.rondebruin.nl/tips.htm. Look at copy/paste/merge
to find examples of these functions.

Cliff Edwards

 

[Peter T]

As you say the LastCell and UsedRange can extend below and to the right of
the last cell that has a value. Normally that's due to formats. To get the
last cell with an entry try -

With ActiveSheet
lastCol = .Cells.Find("*", .Range("A1"), xlFormulas, xlPart, _
xlByColumns, xlPrevious, False).Column
lastRow = .Cells.Find("*", .Range("A1"), xlFormulas, xlPart, _
xlByRows, xlPrevious, False).Row
End With

Above should work 99.99% of the time but in very rare scenarios can error.

Regards,
Peter T