diff between used range row and lastrow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Junior728 - 22 Nov 2007 08:21 GMT

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,

Reply to this Message

ward376 - 22 Nov 2007 09:04 GMT

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

Reply to this Message

Peter T - 22 Nov 2007 10:33 GMT

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

> Hi ,
>
[quoted text clipped - 4 lines]
>
> thanks,

Reply to this Message