Useful VBA – Splitting a list of data into columns in Excel

Hi all,

Often on my journey with Dynamics GP I come across a situation, as we all do, where I think “Shouldn’t there be an easy/faster way to do this?”.

The answer is often “Yes” and there usually is, I just haven’t learned how to do it easier/faster yet. I had a situation where I had just finished using a Microsoft Word Mail Merge to create a Dynamics GP macro to set email settings on a long list of suppliers and I wanted to send a complete list of suppliers to a client in an email.

However, I had all of the information in one column and didn’t want to email a long list, but instead a table.

To google! I found this page on ExtendOffice.com

Multiple solutions on there, including a bit of VBA which will do this for you.

Like most things code, where possible I like to go through the solution to try and understand it, so I re-wrote the code manually. Here is my result based on the code on ExtendOffice.com.

Sub SplitData()
 Dim iRng As Range
 Dim oRng As Range
 Dim funcRow As Integer
 Dim funcCol As Integer
 Dim funcArray As Variant
 
 Set iRng = Application.Selection
 Set iRng = Application.InputBox("Input Data", "Data you would like to split", iRng.Address, Type:=8)
 
 funcRow = Application.InputBox("Rows Per Column", "How many rows do you want in each column?")
 
 Set oRng = Application.InputBox("Output Cell:", "Cell to start output", Type:=8)
 Set iRng = iRng.Columns(1)
 
 funcCol = iRng.Cells.Count / funcRow
 
 ReDim funcArray(1 To funcRow, 1 To funcCol + 1)
 
 For i = 0 To iRng.Cells.Count - 1
 xValue = iRng.Cells(i + 1)
 iRow = i Mod funcRow
 iCol = VBA.Int(i / funcRow)
 funcArray(iRow + 1, iCol + 1) = xValue
 Next
 
 oRng.Resize(UBound(funcArray, 1), UBound(funcArray, 2)).Value = funcArray
End Sub

For example, I have a column of numbers 1-100 in column A and want to split these into 15 rows per column. If I run the above VBA and in the prompts choose A1:A100 and then 15 columns output to B1, I get this:

As you can see, the information is transposed to 7 columns and leaves your original data intact in case you need it. Handy!

Share on Social Media: