VBA code for cut and paste in a loop

[Georgia1]

Hi Knowledgeable people

I am a novice Excel VBA user who is trying to create a cut and paste procedure that moves every second cell in a column of data up adjacent to the first cell and then deletes the blank line.

ie. Raw data in column A is:
Number 1
Description 1
Number 2
Description 2
Number 3
Description 3
etc

I want to move "description 1" into Column B adjacent to "Number 1" and then delete the empty line. Then move "description 2 into Column B adjacent to "Number 2" and delete the empty line. etc

I managed to write a macro to do this as a one off, but I get into horrible trouble when I try to repeat it. I just don't know what I am doing the items in under "number" are not different (in terms of the types of characters) to those under description. I suspect it is a backwards loop that I need and I don't know how to write that and put a cut, paste and delete procedure in the middle of it.

Many thanks to anyone who can start me on the right track

Georgia

 

[jim may]

Perhaps a bit technically lacking, but this seems to work (only according to your specs)..

Sub Foo()
Dim i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR).Select
Do
ActiveCell.Cut
On Error Resume Next
ActiveCell.Offset(-1, 1).Select
ActiveSheet.Paste
On Error GoTo errHandler
ActiveCell.Offset(-1, -1).Select
Loop Until ActiveCell = Range("A1")
Exit Sub
errHandler:
Range("A" & LR).Select
For i = LR To 2 Step -1
    If Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete
Next i
Application.ScreenUpdating = True
End Sub

 

[Georgia1]

Hi Jim
It worked! Thankyou so much for your assistance.

I'll go through it and see if I can work out how it all works!

Georgia