How to convert split formula in vba in their respective columns

< Back to Search results

 Category: [Excel]  Demo Available 

How to convert split formula in VBA in their respective columns




The user has a sheet where the user needs to split columns A into 2 distinct columns in B and C that is there reference will be in columns B and the serial no in column C ,.

How to convert the actual formula in VB to last data row as it consists of 15000 rows.






Option Explicit

Sub Macro1()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & lr).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub



An alternative method

Sub SplitReference()
    Dim x, y(), i As Long
    With Sheets("INVOICE SPLIT")
        x = .Cells(1).CurrentRegion
        For i = 2 To UBound(x, 1)
            ReDim Preserve y(1 To 2, 1 To i - 1)
            y(1, i - 1) = Split(x(i, 1), "/")(0): y(2, i - 1) = Split(x(i, 1), "/")(1)
        .[b2].Resize(UBound(y, 2), UBound(y, 1)) = Application.Transpose(y)
    End With
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman and KjBox.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use VBA - Split Worksheets and Display SaveAs Prompt
How to create VBA to split data to their respective columns with character restriction
Split row into many rows using trigger in particular row cells
How to find last non blank cell & not affect split screen view


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.






stars (0 Reviews)