Excel VBA- Split Text To Columns

EXCEL VBA TO SPLIT WORDS INTO SEPARATE COLUMNS

Region Brand	Region	Brand
Central Dell	Central	Dell
South HP	South	HP
North Acer	North	Acer
Central Acer	Central	Acer
West Acer	West	Acer
Central Samsung	Central	Samsung
North Acer	North	Acer
South Samsung	South	Samsung
South TRUE	South	TRUE
South Dell	South	Dell
#NAME?	South	Dell
West Samsung	West	Samsung

Sub pSplitFirstLast()

    Dim varData

    Dim lngR As Long

    Dim strFirst As String

    Dim strLast As String

    Dim strFname() As String

    Dim strLname() As String

    Dim lngC As Long

    

    varData = Sheet1.Range("Start").CurrentRegion

    

    ReDim strFname(1 To UBound(varData))

    ReDim strLname(1 To UBound(varData))

    

    On Error Resume Next

    For lngR = LBound(varData, 1) To UBound(varData, 1)

        lngC = lngC + 1

        strFirst = Split(varData(lngR, 1))(0)

        strFname(lngC) = strFirst

        strLast = Split(varData(lngR, 1))(1)

        strLname(lngC) = strLast

    Next

    On Error GoTo 0

    

    Sheet1.Range("B1:B" & UBound(varData)) = Application.WorksheetFunction.Transpose(strFname)

    Sheet1.Range("C1:C" & UBound(varData)) = Application.WorksheetFunction.Transpose(strLname)

    

    Erase strFname

    Erase strLname

End Sub