• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split multiple columns based on range

Dear Sir,

I got macro which can split column based on value.
Columns to be split (as in trail workbook).
Range of columns to be split from EA to FX.
No of time (no of times) to be copied from first column is in B1 for EA, d1 for EB and so on.
Out put for each split column start from column GA.

Means move input range one column each time (EA till FX).
Values for splitting column B1, d1, f1, h1 till CY1,
Output start from GA.

I have marked yellow color for output of two macros.
Private Sub SplitColumn1()
    Dim rng As Range
    Dim InputRng As Range
    Dim OutRng As Range
    Dim xRow As Integer
    Dim xCol As Integer
    Dim xArr As Variant
    Dim i As Integer
    Dim xvalue As Variant
    Dim iRow As Integer
    Dim iCol As Integer

    On Error Resume Next

    Set InputRng = Sheets("Sheet1").Range("EA1:EA2200")
    xRow = Sheets("Sheet1").Range("b1")
    Set OutRng = Sheets("Sheet1").Range("EC1")

    Set InputRng = InputRng.Columns(1)
    xCol = InputRng.Cells.Count / xRow
    ReDim xArr(1 To xRow, 1 To xCol + 1)
    For i = 0 To InputRng.Cells.Count - 1
        xvalue = InputRng.Cells(i + 1)
        iRow = i Mod xRow
        iCol = VBA.Int(i / xRow)
        xArr(iRow + 1, iCol + 1) = xvalue
    OutRng.Resize(UBound(xArr, 1), UBound(xArr, 2)).Value = xArr

End Sub

Thank you very much for your help.


Last edited:


Active Member
Hi paneliyadhruv, please clarify what you are actually trying to achieve.
Is the middle step (columns ea to fx) a requirement, or is it just a middle step?
It would make more sense to just go from your first input to your final output.
Also, what is the intention for the output data? It may be that one of us can suggest a better approach than populating thousands of cells with these words.
Dear sir,
Based on help from this forum first macro was added in my trial sheet. Which generates output from EA to FX. So, no changes is required in first macro.

I need to split each column from EA to FX.
For e.g. EA column split in 11 columns from GA onwards based on value present in B1.
For column EB based on value present in D1 and output in 12 column from GA.
For EC based on F1 and so on.
I am not familiar with macro. How to move range of input and output.
I got macro to split single column to multiple columns I.e. second macro. This macro need modification to move range of input and output.
Purpose is to make 75 sheets of labels like output based on various input in form of text, number series etc. I am creating each step one by one as I do not have much knowledge of macro. Based on help received from all excel vba gurus. I am trying to something step by step.

Marc L

Excel Ninja
Hi !

Some helpers can do nothing without a crystal clear explanation
- detail each step clearly & technically - and
an accurate attachment (as yours has same data in every column !).

But if you know how to do it manually, just activate the Macro Recorder
and operate manually : you will get your own code base …

Marc L

Excel Ninja

Maybe I catch it if your attachment result is only for a single column,
I will give it a try later …​
Dear sir and all,
I didn't know macro much. If you help me how to move range in simple method. I will do trial and error to get desire result.

Marc L

Excel Ninja
Move ?! As your explanations make us confused :
post a before workbook with at least 2 columns of different data
and an after workbook accordingly with the exact result expected.

If you use color for result choose two different colors for the columns
whatever for before or after workbooks …

And it seems columns EA to FX are useless / superfluous
- that's just clutter ! - as the data are already in previous first columns !
Like cells B2:B10 (as cell B1 is enough), D2:D10, F2:F10, and so on …

So just from column A and cell B1 only two codelines are necessary
to transpose data to GA1:GJ10 without any help from column EA

Once you understand your need it would be easy to clearly explain it
or, by default, just attach a before source workbook
and an after workbook for expected result
without any intermediary step outside the result area !
As yet asked in post #3 by Stevie

How are created column A and cell B1 ? Who is the author ?
Dear sir,
I will upload full sheet with details. Let me give some time.
You are correct, as data from column a to cy generated in each columns in transpose way. There is is no need to generate value based on first macro and then split. I don't get such macro hence, I have used two macro to get desire result. If you can help me get split/generate result in each columns that also sufficient for me.

Marc L

Excel Ninja
Question : why creating source data (sheet Calc2) in a vertical way (column)
as you need data in a horizontal way (row) ?!

For example column A devoluted to row number to repeat,
column B always blank and first data in C1, second in D1, and so on …

As a smart worksheet should be in the same way,
as with a smart worksheet it's easier to create a smart code …

Question : in your last attachment, if column A and only cell B1 are
the source data, what is the final expected result,
where is it in your attachment ?
Dear sir, column A and only B1 required, similarly for column C and d1 and so on. Because I got macro like that so have changed my input to suit macro. Final output in G1 sheet for column a to b, G2 similarly for other columns in sheet G2 to G25. Only G1 I have created complete set of macro. For G2 sheet only data from calc2 sheet not move as it is similar to last macro.

Marc L

Excel Ninja
In my opininion all your actual codes go to trash …

Ok so for a smart code it needs a couple of smart worksheets !
Create a new workbook with only two worksheets :
• #1 for source data & parameters
• #2 for a formated template …

From your last attachment in worksheet G1 or G2
- depending on the best formatting -
copy cells A1:M4 and paste to the new workbook sheet #2 in cell A1.
If there are data, clear any content but keep the formatting.
This sheet #2 is the template used to create result worksheets like G1 …

Now for sheet #1 according to your previous attachment,
cell A1 contains the worksheet name to create (G1),
cell B1 contains the copy # for the left part (10) and
cell C1 contains the copy # for the right part (5).
Columns D & K stay always blank …
Cells E1:J4 are the source data for the left part
and L1:Q4 for the right part.
For example you can copy from your previous attachment in sheet G1
cells A1:M4 and on the new workbook in sheet #1 select the cell E1
then apply a paste special via the right click on Values only :
now you have the source data without any formatting.
Row #5 stays blank and cells A2:A4 too …
G2 starts in cell A6 following the same horizontal way as G1.

I need at least two complete parameters (G1 & G2) in sheet #1
but you can add some more if you want.
Once achieved, save this new workbook and attach it here …

With those smart worksheets, the procedure to create worksheets G1,
G2, and so on … will need less than 30 codelines.

Marc L

Excel Ninja
If you just follow my previous post easy directions at child level
next code will rocks !
But if you do nothing as expected, do not be surprised …​
Sub Demo1()
    Dim Rg As Range, V, M%
    Set Rg = Sheet1.[A1]
    Application.ScreenUpdating = False
  While Rg.Value > ""
    V = Application.Index(Rg.Resize(, 3).Value, , 0)
    M = Application.Max(V(2), V(3)) * 5
    If Evaluate("ISREF('" & V(1) & "'!A1)") Then Worksheets(V(1)).UsedRange.Clear Else Sheets.Add(, Sheets(Sheets.Count)).Name = V(1)
        With Worksheets(V(1))
             Sheet2.Rows("1:5").Copy .Rows("1:" & M)
             If M > 5 Then .Range("A6:M" & M).Clear
            .[A1:M4].Value = Rg(1, 5).Resize(4, 13).Value
             If V(2) > 1 Then .[A1:F5].Copy .Range("A6:F" & V(2) * 5)
             If V(3) > 1 Then .[H1:M5].Copy .Range("H6:M" & V(3) * 5)
        End With
    Set Rg = Rg.End(xlDown)
    Set Rg = Nothing
    Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
Dear Sir,

Please share excel sheet. I have shared complete G1 and G2 sheets input and output in post 19 with macros as trail updated sheet.
Dear Sir,
Values in B, D, F, H, J, L and so on not static. Increase or decrease as per requirement. Sheet G1 and G2 are template for output of data.

Marc L

Excel Ninja
I have shared complete G1 and G2 sheets input and output in post 19 with macros as trail updated sheet.
As it is very not the post #18 expected workbook !
Obviously without it I can't test my procedure but I'm confident …

Values in B, D, F, H, J, L and so on not static. Increase or decrease as per requirement
Why this very important information comes only in post #24
as it must be written in the first post ? Too late ! …
Since the beginning of this thread, several times we asked for a complete
and crystal clear technical explanation and we have few answers
but with a lack of information …

So I had two choices : leave this thread like it started
or take the bull by the horns starting from a brand new workbook
as written in post #18 after a child succeed to follow my directions …

I won't waste any time for a so easy beginner copy data
as you can already operate manually and just activating
the Macro Recorder you can get your own code base like any beginner can …