Paste Special Values To First Blank Row

**gshea** · 08-03-2012, 02:37 AM

I am trying to paste special values+to the first blank row starting in one cell and ending in the last cell, from one sheet to another sheet. I have most of the code but the "offset line" seems to be giving me alert

s" as missing range. Can anyone help with what might be missing? I have attached the file.

Please Login or Register  to view this content.

**rscsmith** · 08-03-2012, 02:46 AM

Hi George

If I've understood your correctly, the following should work:

Please Login or Register  to view this content.

The Offset line should have been xlDown to take you to the last row of existing data. Then, Offset(1,0) to offset that row by one (i.e. get to the first blank).

btw. If B4 is blank (i.e. you have no existing data) you will get an error as xlDown will take you to the last row on the worksheet and you will then try and offet(1,0) to a row that doesn't exist. You could include an If statement so that if B4 is blank it doesn't do the Offset, it just pastes there.

Best regards, Rob.