macro to move cell contents and then delete the row

[Guest]

I have a single column of data located in Column A similar as follows:

"Column A" "Column B" "Column 3" "....."

Example1*
* data1
Example2*
* data2
Example3*
* data3

and so on. What I want to do is have a macro to move the contents as follows:

"Column A" "Column B" "Column 3" "....."

Example1* * 1

Example2* * 2

Example3* * 3

Then I want to delete the empty rows where the data used to be. Is this
possible?

Thanks

 

[Ron de Bruin]

You can try this with

* data1

starting in row 2 in the A column

This example loop through 100 rows

Sub Testing()
For I = 2 To 100 Step 2
Cells(I, 1).Cut Cells(I - 1, 2)
Next I
On Error Resume Next 'In case there are no blank cells
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[Ron de Bruin]

Note that I use

Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Change
Columns("A").
To a range if you not want to check the whole column

 

[Ron de Bruin]

Like this

lr = is the last cell with a value in A

Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 2)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub