VBA: Copy data from every other row, paste it in previous row, delete copied row

[JohnJames]

Hi all!

A team I work with sends us reports that we want to put in a database, unfortunately the data for one entry is on multiple lines and it had data that was uneeded. I figured out how to delete the unneeded data and get rid of the blank spaces, just having trouble with the last part which is copying the data in every other row, pasting it in the previous row then deleting the copied row. Fig.1 is what I have currently, I want my data to look like Fig.2.

I found the following code, but don't know how I could make this work for x amount of rows or my range:

Set Rng = Range("B7")
Range(Rng.Address).Offset(-1, 0).Value = Rng.Value

Please look at my

Fig.1
[TABLE="width: 500"]
<tbody>[TR]
[TD]dataR1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]data11[/TD]
[TD]data12[/TD]
[TD]data13[/TD]
[TD]data14[/TD]
[TD]data15[/TD]
[/TR]
[TR]
[TD]dataR2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]data21[/TD]
[TD]data22[/TD]
[TD]data23[/TD]
[TD]data24[/TD]
[TD]data25[/TD]
[/TR]
[TR]
[TD]dataR3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]data31[/TD]
[TD]data32[/TD]
[TD]data33[/TD]
[TD]data34[/TD]
[TD]data35[/TD]
[/TR]
</tbody>[/TABLE]

Fig.2
[TABLE="width: 500"]
<tbody>[TR]
[TD]dataR1[/TD]
[TD]data11[/TD]
[TD]data12[/TD]
[TD]data13[/TD]
[TD]data14[/TD]
[TD]data15[/TD]
[/TR]
[TR]
[TD]dataR2[/TD]
[TD]data21[/TD]
[TD]data22[/TD]
[TD]data23[/TD]
[TD]data24[/TD]
[TD]data25[/TD]
[/TR]
[TR]
[TD]dataR3[/TD]
[TD]data31[/TD]
[TD]data32[/TD]
[TD]data33[/TD]
[TD]data34[/TD]
[TD]data35[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated!

 

[JackDanIce]

Try:

Dim LR  As Long
Dim LC  As Long

LR = Application.Max(Cells(Rows.count, 2).End(xlUp).row, 7) - 6
LC = Cells(7, Columns.count).End(xlToLeft).column - 1

Cells(6, 2).Resize(LR, LC).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp