Solved Ignore blank cells in copy and paste

January 11, 2013 at 06:51:28
Specs: Windows 7
I have 6 rows and only one has a number. I'd like to copy and paste additional rows but when i do it adds 6 to the row number. Example: The A1 cell is =item!A2 then there is 5 blank lines. When I copy and paste the next set of rows I get =item!A8. What I need is =item!A3

See More: Ignore blank cells in copy and paste

Report •

January 11, 2013 at 08:59:25
✔ Best Answer
Enter this in A1, then copy your group of 6 cells and paste them in A7, and so on.


Since the ROW function will return the Row number that the formula resides in,

(ROW()-1)/6+1 will return 1 in A1, 2 in A7, 3 in A13, etc.

For example:

In A1:

ROW() = 1

ROW()-1 = 0

0/6 = 0

0+1 = 1

In A7:

ROW() = 7

7 - 1 = 6

6/6 = 1

1 + 1 = 2

and so on.

That value will then be used as the Row offset from item!A1. With a 0 Column offset, you will see the values from item!A2, item!A3, item!A4, etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 11, 2013 at 12:35:49
This worked perfectly. Wish I would have asked earlier rather than spend tons of time on it. Thank you so much.

Report •
Related Solutions

Ask Question