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

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

Split imported text in Excel, facing issue with large data

ThrottleWorks

Excel Ninja
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.

Code:
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
 
Does this do it?:
Code:
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
 
Back
Top