Split imported text in Excel, facing issue with large data

[ThrottleWorks]

Hi,

I am trying to split imported text in Excel.
Am using below code to perform the task.
This code runs fine till number of rows are around 3,000.
Post 3,000 rows of data, macro gets stuck.
Number of columns are 110.

How do I resolve this issue. Can anyone please help me in this.

Sub SplitName()
    Dim MacroBook As Workbook
    Dim MapSht As Worksheet
    Dim TempSht As Worksheet
    Dim TempLr As Long
        
    Dim MyArray() As String, MyString As String, i As Variant, N As Integer
    
    Set TempSht = ThisWorkbook.Worksheets("Temp")
    TempLr = TempSht.Cells(Rows.Count, 1).End(xlUp).Row - 1
    
    Dim TempRng As Range
    Dim TRng As Range
    
    Set TempRng = TempSht.Range("A2:A" & TempLr)
    
    For Each TRng In TempRng
        'Application.StatusBar = TempLr - TRng.Row
        MyArray = Split(TRng, "|")
        
        i = i + 1
        For N = 0 To UBound(MyArray)
            TempSht.Range(TempSht.Cells(TRng.Row, i), TempSht.Cells(TRng.Row, i)) = MyArray(N)
            i = i + 1
        Next N
        i = 0
    Next TRng
    'Application.StatusBar = False
End Sub

 

[p45cal]

Does this do it?:

Sub SplitName()
Dim TempSht As Worksheet
Dim TempLr As Long
    
Set TempSht = ThisWorkbook.Worksheets("Temp")
TempLr = TempSht.Cells(Rows.Count, 1).End(xlUp).Row - 1 'is the -1 necessary?
TempSht.Range("A2:A" & TempLr).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="|"
End Sub

 

[ThrottleWorks]

Hi @p45cal sir, thanks a lot for the help. This is working great.