Splitting date and time I asked a question on Stack OverFlow and I got an answer to my question but then I found out the program is crashing because I have 250k data points on my Excel sheet. I was wondering what I can do to make it so it won’t crash. I'm hoping I can understand what I can do to improve the code.

Sub CompareTime()

Dim ws As Worksheet

Dim lastRow As Long
Dim Count As Long
Dim test As Double

Set ws = ActiveSheet


'Find last data point
With ws
    .Columns(2).Insert
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    For Count = 5 To lastRow
        'split date
        test = .Cells(Count, 1).Value2
        .Cells(Count, 1).Value2 = Int(test)
        .Cells(Count, 1).NumberFormat = "m/d/yyyy"
        .Cells(Count, 1).Offset(0, 1).Value2 = test - Int(test)
        .Cells(Count, 1).Offset(0, 1).NumberFormat = "hh:mm AM/PM"

    Next Count
End With


End Sub
Turning off Application.ScreenUpdating and Application.Calculation will greatly increase the speed.  But reading the data into an Array, modifying it and writing the Array back to the Worksheet is the real key to speed.  

Recommended viewing: Excel VBA Introduction Part 20.2 - Application Events, Excel VBA Introduction Part 25 - Arrays

Sub CompareTime()
    'Optimize Setting for faster data writing
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    Dim Target As Range, ws As Worksheet
    Dim x As Long
    Dim results As Variant

    Set ws = ActiveSheet

    'Find last data point
    With ws
        .Columns("B:C").Insert 
        .Columns("B:B").NumberFormat = "mm/dd/yy;@" 
        .Columns("C:C").NumberFormat = "h:mm:ss;@"

        Set Target = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 3)
        results = Target.Value2

        For x = 1 To UBound(results)
            results(x, 2) = Int(results(x, 1))
            results(x, 3) = results(x, 1) - results(x, 2)
        Next

        Target.Value2 = results
    End With

    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub



  Set Target = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 3) 




Code Explaination


.Cells(.Rows.Count, "A").End(xlUp)    


Target the last non-empty cell in Column A

.Range("A5", .Cells(.Rows.Count, "A").End(xlUp))
= Target Range from A5 to last non-emtpy cell in Column A
.Resize(, 3)
= Resize the Target range to include 2 more Column to the right same number of rows
results = Target.Value2   


Create an array of values from the Target range.  
.Value 2 is more effiecent than .Value because it ignores formatting

Target.Value2 = results   


Assign the modified values from the results Array back to the Target range.