Results 1 to 2 of 2

Thread: Reformatting data in Excel

  1. #1

    Post Reformatting data in Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    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
    Last edited by p45cal; 2014-04-19 at 10:59 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •