Reformatting data in Excel

PBrooks · Apr 19, 2014

I have been trying to write Excel VBA code to manipulate some Excel data and have been unsuccessful.
What I want to do is the following (in plain English):

I have data that looks like this right now:

Question 1
Comment 1
Question 2
Comment 2
Question 3
Comment 3
...etc...

I would like to manipulate this data so that the VBA code goes down the column the number of rows I specify starting from the
Cell with "Question 1" in it then:
Moves each Comment line to be in the cell right adjacent to the Question cell and then:
Delete the blank row created by this move and repeat the process. The end result would look like this:

Question 1 Comment 1
Question 2 Comment 2
Question 3 Comment 3
....etc.....

Can anyone help me with the code for this?
Thanks in advance

p45cal · Apr 19, 2014

If you don't mind the entire rows being deleted, then select the single column of cells with the Questions/Comments (so and even number of cells will be selected (and the top most cell will be a Question)), then run:

Code:

Sub blah()
With Selection
  .Cells(1).Offset(, 1).FormulaR1C1 = "=R[1]C[-1]"
  .Cells(1).Offset(1, 1) = Empty
  .Cells(1).Offset(, 1).Resize(2).AutoFill Destination:=.Offset(, 1)
  .Offset(, 1).Value = Selection.Offset(, 1).Value
  .Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Reformatting data in Excel

PBrooks

New member

p45cal

Super Moderator