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

**rxpsych** · 08-20-2013, 02:19 AM

**Excel Fox** · 08-20-2013, 02:40 AM

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.

**rxpsych** · 08-20-2013, 03:30 AM

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

**Excel Fox** · 08-20-2013, 07:48 PM

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

**rxpsych** · 08-20-2013, 09:16 PM

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.

**Excel Fox** · 08-21-2013, 12:18 AM

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

**snb** · 08-21-2013, 01:05 AM

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

**rxpsych** · 08-21-2013, 03:15 AM

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.

**snb** · 08-21-2013, 01:53 PM

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