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

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.

Thank you for your assistance. Here is a link to my file.... https://www.dropbox.com/s/co1xapy9z42yksu/Book1.xlsx

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

---

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.

Quote:

---

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

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

---

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.

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

---

Thank you snb. That worked perfectly. Everything copied below the first row exactly as it needed to be.

I have a similar problem as rxpsych even bigger as i have multiple such columns of patients data . But i dont know how to apply the code given here. can somebody explain the method of applying the code. Screenshots will be more helpful. Thanks in advance.