Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA To Separate One Long Row Data Into Multiple Rows Of Fixed Columns

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Question VBA To Separate One Long Row Data Into Multiple Rows Of Fixed Columns

    Good afternoon,

    I copied and pasted a quiz I am set to give on Wednesday (in 2 days) into Excel and it is appearing as one very long row of cells. The question appears in the first cell and then each multiple choice answer (A, B, C, D, etc) are each in a separate cell after. Then it repeats for 50 questions. I would cut and paste them into different rows if it weren't so many questions. So, I have one row with cells A1 to MY1. Each question represents 6 cells in a row (Question + 5 possible answers). I just need to insert a new column after each 6th cell. I've searched the web and many forums and thought I'd find a simple answer to my dilemma, but cannot. I know nothing about writing VBA code, but I know where and how to input it into a macro. Can anyone assist me in task? Thank you in advance.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
    https://www.youtube.com/watch?v=jdPeMPT98QU
    https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-20-2023 at 03:02 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can you attach the file on to a file share site like 4share and post the link here? If the questions are confidential, you can create a sample that represents your original file.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Thank you for your assistance. Here is a link to my file.... https://www.dropbox.com/s/co1xapy9z42yksu/Book1.xlsx

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Use this in a module

    Code:
    Sub InsertColumnsAtN()
    
        Dim lngCol As Long
        
        For lngCol = 289 To 7 Step -6
            ActiveSheet.Columns(lngCol).Insert
        Next lngCol
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Unfortunately, it didn't do anything but blink and inserted a whole lot of empty columns. I may have explained what I needed incorrectly. I wanted all of my questions to appear in column A, and each answer to appear in columns B,C,D,E, and F, respectively. Sorry for the miscommunication. I need it this way to import it into some new testing software that I'm trying out. Thank you.
    Last edited by rxpsych; 08-20-2013 at 09:39 PM.

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    I just need to insert a new column after each 6th cell.
    Clearly, that doesn't reflect in your statement here!!

    EDIT: So if you expect a solution, I'd suggest you post a sample of the expected output. I will then provide a solution as it deems fit
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    Sub M_snb()
      sn=sheets(1).cells(1).currentregion
      redim sp( ubound(sn,2)\5,4)
      
      for j=1 to ubound(sn,2)
         sp((j\5,j mod 5)=sn(1,j)
      next
      cells(10,1).resize(ubound(sp)+1,ubound(sp,2)+1)=sp
    End Sub

  8. #8
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Excel Fox, I sincerely apologize for my miscommunication. I should have said a "return" after the sixth cell, as happens in a Word document to go to the next line down. I know Excel is not Word and the lingo is different, so please forgive my ignorance.

    snb... Thank you for the suggested code, but it throws a Syntax error in the line "sp((j\5,j mod 5)=sn(1,j)"

    Thank you both for your help, but since time is growing short, I've saved the Excel sheet as a CSV and opened it in Notepad. I then just hit ENTER after the sixth comma and saved it. Then I opened it back up in Excel and it did what I needed it to do. Sometimes the manual way takes less time than trying to figure out the easy way.

    I do truly appreciate your willingness to help. I'd still love to figure it out at some point because I'm sure it's going to become an issue again. Just so I can be more clear, I have one long row where in each cell there is: Q1, 1A, 1B, 1C, 1D, 1E, Q2, 2A, 2B, 2C, 2D, 2E, Q3, 3A, 3B, etc. up to Q50 with A to E multiple choice answers for each. I need to have 50 rows where all the Questions (Q1 to Q50) are in one column, all A answers are in the next column, all B answers in the next column, etc, up to E answers. That is why I saved as a CSV and just hit enter after every "E" answer. It put the next question on the next row, but I'm sure there's a VBA code to do it easier. Thanks.

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Use:

    Code:
    Sub M_snb()
        For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column Step 6
          Cells(j \ 6 + 2, 1).Resize(, 6) = Cells(1, j).Resize(, 6).Value
        Next
    End Sub
    Last edited by snb; 08-21-2013 at 02:08 PM.

  10. #10
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Thank you snb. That worked perfectly. Everything copied below the first row exactly as it needed to be.

Similar Threads

  1. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  2. Replies: 3
    Last Post: 07-29-2013, 11:32 PM
  3. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  4. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  5. Replies: 2
    Last Post: 06-14-2012, 04:10 AM

Posting Permissions

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