Array returning #N/A

[Jotoya]

Hello,

I receive an automatically generated report that contains a single sheet with around 540,000 used rows. In column J the file contains a date/time format that looks like this: 2023-08-02T16:26:42 I have emphasized the "T". The file is a .csv file. I have used the below code to remove the "T" in another csv file with larger number of rows used (790,000) successfully but with this file, after about 150,000 rows it starts generating a #N/A. I have checked for extra spaces and all good in that front.

Sub RemoveTFromData ()

    Dim v As Long
    Dim LastRowJ As Long
    Dim arrJ() As Variant
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
'   Define variables.
    LastRowJ = Sheets("Report").Range("J" & Rows.Count).End(xlUp).Row
    
'   Define and load Arrays to be used for columns J.
    arrJ = Application.Transpose(Sheets("Report").Range("J3:J" & LastRowJ))

'   Loop through arrays to remove "T" value and re-format column J.
    For v = 1 To UBound(arrJ)
        arrJ(v) = Replace(arrJ(v), "T", " ")
    Next v

'   Write arrays back to column.
    Sheets("Report").Range("J3:J" & LastRowJ) = Application.Transpose(arrJ)
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Any assistance is greatly appreciated.

 

[Alex Blakenburg]

You are making that much harder than it needs to be, try this:
(this should be very fast)

Sub RemoveTFromDatav02()

    Dim wsRpt As Worksheet
    Dim rngJ As Range
    Dim LastRowJ As Long
    Dim arrJ() As Variant
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
'   Define variables.
    Set wsRpt = Worksheets("Report")
    LastRowJ = wsRpt.Range("J" & Rows.Count).End(xlUp).Row
    Set rngJ = wsRpt.Range("J3:J" & LastRowJ)
 
'   Define and load Arrays to be used for columns J.
    arrJ = rngJ.Value2
'   Remove "T" value and re-format column J.
    arrJ = Application.Substitute(arrJ, "T", " ")

'   Write arrays back to column.
    rngJ.Value2 = arrJ
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

 

[Jotoya]

You could also just do a Find/Replace as below. For my 400,000+ row test data it did take longer than the array method above (10 seconds v about 1.2 seconds) but not too long a wait.

Sub Replace_T_v1()
  Columns("J").Replace What:="T", Replacement:=" ", LookAt:=xlPart
End Sub

Many, many thanks Peter! I will most certainly give it a shot here in a bit and report back!

 

[Peter_SSs]

(this should be very fast)

I agree, but for me not quite as fast as the code in post 9 (variously tested 10-25% slower)

 

[Peter_SSs]

Many, many thanks Peter! I will most certainly give it a shot here in a bit and report back!

You're welcome. Note though that although that code is very short, it isn't anywhere near as fast as post 9 or post 11.

 

[Jotoya]

You are making that much harder than it needs to be, try this:
(this should be very fast)

Sub RemoveTFromDatav02()

    Dim wsRpt As Worksheet
    Dim rngJ As Range
    Dim LastRowJ As Long
    Dim arrJ() As Variant
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
'   Define variables.
    Set wsRpt = Worksheets("Report")
    LastRowJ = wsRpt.Range("J" & Rows.Count).End(xlUp).Row
    Set rngJ = wsRpt.Range("J3:J" & LastRowJ)
 
'   Define and load Arrays to be used for columns J.
    arrJ = rngJ.Value2
'   Remove "T" value and re-format column J.
    arrJ = Application.Substitute(arrJ, "T", " ")

'   Write arrays back to column.
    rngJ.Value2 = arrJ
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Many thanks Alex! I'll give your method a go as well. I appreciate you posting it.

 

[Jotoya]

You're welcome. Note though that although that code is very short, it isn't anywhere near as fast as post 9 or post 11.

Once again, very appreciative of yours, @Peter_SSs and @Alex Blakenburg solutions. Both worked incredibly fast. So thankful!

 

[Alex Blakenburg]

You're welcome. Glad we could help.