1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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

    Hui...

  3. 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

Discussion in 'VBA Macros' started by paneliyadhruv, Jul 6, 2018.

  1. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
    Code (vb):
    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
        Next
        OutRng.Resize(UBound(xArr, 1), UBound(xArr, 2)).Value = xArr

    End Sub

    Thank you very much for your help.

    Attached Files:

    Last edited: Jul 6, 2018
  2. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    Sorry, I have uploaded sample file.
  3. Stevie

    Stevie Active Member

    Messages:
    116
    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.
    Marc L and paneliyadhruv like this.
  4. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  5. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    If possible make changes simple so I can learn and apply it to other macro.
  6. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  7. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    Dear all expert,

    I am waiting for your valuable inputs.
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    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 …
    paneliyadhruv likes this.
  9. Marc L

    Marc L Excel Ninja

    Messages:
    4,257

    Maybe I catch it if your attachment result is only for a single column,
    I will give it a try later …​
  10. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    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 ?
  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    Just see in VBA inner help Range.Cut method and its sample, easy …


    Or as I yet wrote :

  13. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  14. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    Stevie was right since the beginning …

    But before to add any new attachment :
  15. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    Dear Sir,

    I am the author. I have created all columns based on my requirement and understanding of excel. Please find my updated worksheet.

    Attached Files:

  16. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    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 ?
  17. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  18. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    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.
  19. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    Dear Sir,

    Please find updated sheet for your valuable inputs. I have included G1 and G2 data input and output.

    Attached Files:

  20. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    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 …​
    Code (vb):
    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)
      Wend
        Set Rg = Nothing
        Application.ScreenUpdating = True
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
  21. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    run time error 13 at M = Application.Max(V(2), V(3)) * 5
  22. Marc L

    Marc L Excel Ninja

    Messages:
    4,257


    Oh you did not follow post #18 directions …​
    paneliyadhruv likes this.
  23. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  24. paneliyadhruv

    paneliyadhruv Member

    Messages:
    50
    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.
  25. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    As it is very not the post #18 expected workbook !
    Obviously without it I can't test my procedure but I'm confident …

    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 …

Share This Page