# 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

#1
January 11, 2013 at 08:59:25
 Enter this in A1, then copy your group of 6 cells and paste them in A7, and so on.=OFFSET(item!\$A\$1,(ROW()-1)/6+1,0)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() = 1ROW()-1 = 00/6 = 00+1 = 1In A7:ROW() = 7 7 - 1 = 66/6 = 11 + 1 = 2and 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 •

#2
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