Pasting values consecutively

Question

Hello,

I have a column of 10 numbers, each separated by five emtpy rows (cells). So they are every sixth row. I want to copy and paste them into another column but I want them to be consecutive so there are no rows in between them. I've tried features of Paste Value but nothing does this.

Can this be done? Thank you.

Answer 1

Thanks Frank. That worked nicely. I didn't want to use a straight sort since there are columns of numbers that correspond to each other and some of them are negative and some positive and if I just sort then the negative and positive numbers get mixed.

Thanks again

Answer 2

Mike H

I'm a follower of your and Rick Rothsteins's posts and this is awsesome in its simplicity; but only when you have explained it. Another one to keep. Thank you.

Answer 3

Hi,

It would have helped if you had told us where these numbers start and which column but instead of paste try this and drag down.

=INDEX(A:A,(ROW(A1)-1)*6+0)

this assumes your numbers start in A1 and are seperated by 5 rows. The +0 isn't necessary in this scenario but I have included it in case your numbers start in a different row, for example if they started in row (say) 9 the formula would be

=INDEX(A:A,(ROW(A1)-1)*6+9)

the *6 is the row seperation i.e. (number +5blanks)=6

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 4

You could copy and paste the column as is, and then select a cell therein and run a macro similar to this.....just be sure to practice on a copy of your workbook.

Sub DeleteBlankRowsInActiveColumn()

ac = ActiveCell.Column

lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Range(Cells(1, ac), Cells(lr, ac)). _

SpecialCells(xlCellTypeBlanks).Delete

End Sub

hth

Vaya con Dios,

Chuck, CABGx3

Answer 5

Hi,

select all data in the column inclusive empty cells, switch on the autofilter, click the dropdown button, choose not empty from the dropdown list, Ok.

Copy the filtered list and paste it at a new position.

or

sort the list ascending, copy and paste the values.

Regards,

Frank