Paste to last row without defining Lastrow

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi, colleague asked me this and I've seen it before but can't successfully google what I want.


Basically in a single copy operation from one sheet to another you can say, for instance:

VBA Code:
Range("A2").copy Destination:=temp.range("A" & Lastrow + 1)

That's the normal way, or one of them.

What I want to know is how to paste to the bottom of the data set, without setting a new last row. If that above code was in a loop I'd have to switch to the temp tab and re-define the last row each time.

While computationally it takes no time at all, is it not better practice to cut down unnecessary code and instead paste like this:

VBA Code:
Range("A2").copy Destination:=temp.cells(rows.count, "A").end(xlup).row + 1

When I try this, nothing is pasted. Any help? Thanks.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try this instead:
VBA Code:
Range("A2").Copy Destination:=temp.Cells(Rows.Count, "A").End(xlUp).Offset(1,0)
 
Upvote 0
Solution
Excellent thank you!
You are welcome.

I hope it makes sense. Since you were dealing with ranges, it does not really make sense to add "1" to a range (that is a mathematical operation, not a range one).
 
Upvote 0
Surely you'd just increment LastRow inside the loop?
Yeah but that involves switching to the other sheet, declaring the new lastrow and switching back.

I did try Lastrow = temp.cells(rows.count, "A").end(xlup).row but it didn't seem to iterate :(
 
Upvote 0
Yeah but that involves switching to the other sheet, declaring the new lastrow and switching back.
No, it doesn't. All you'd need is Lastrow = LastRow + 1 inside the loop.
 
Upvote 0
No, it doesn't. All you'd need is Lastrow = LastRow + 1 inside the loop.
HAHAAAAAAAAA

Actually made me lol in the office.

I'm such a f'in moron.


Actually, belay that remark!

What if I'm copying varying size of ranges, I'd have to measure the range inside the loop every time, right? Seems easier to be able to paste directly to the last row, whatever that last row is. Cheers.
 
Upvote 0
Just as easy (and probably safer in case you have missing data in the column you are using to establish the last row) to count the range's rows.

Also, to be clear, there would never be a need to actually switch to the other tab just to find the last row.
 
Upvote 0
Just as easy (and probably safer in case you have missing data in the column you are using to establish the last row) to count the range's rows.

Also, to be clear, there would never be a need to actually switch to the other tab just to find the last row.

I didn't think so, but for some reason using Lastrow = temp.cells(rows.count, "A").end(xlup).row did not update with the correct last row.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top