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.

 

[dreid1011]

See if this helps at all: https://stackoverflow.com/questions...at-some-point-it-puts-na-instead-of-the-value

 

[Jotoya]

See if this helps at all: https://stackoverflow.com/questions...at-some-point-it-puts-na-instead-of-the-value

I appreciate the quick response dreid1011. The SO page indicates that the transpose function "has a limit to the length of the array it can manipulate. This limit is somewhere between 65k and 66k columns."

My sheet only has about 12 columns. As I indicated in the OP, the code will run on another csv file containing over 200k more rows.

The csv file I'm having trouble with has empty cells and for a moment I thought that may have been the problem. I removed them and nothing.

Thanks again for the quick response and being willing to assist. I appreciate it.

 

[PeteWright]

@Jotoya
Working with Arrays is always a bit error-prone.

I wrote a code that deals with the cell values instead of writing and reading from an array.
Can you try it?

Sub CleanTimeString()
   Dim ws As Worksheet: Set ws = Sheets("Report")
   Dim i As Long
   Dim n As Long: n = ws.Range("J" & Rows.Count).End(xlUp).Row
   On Error Resume Next
   For i = 1 To n
      ws.Cells(i, 1).Value = Replace(ws.Cells(i, 1).Value, "T", " ")
   Next i
End Sub

 

[Jotoya]

@Jotoya
Working with Arrays is always a bit error-prone.

I wrote a code that deals with the cell values instead of writing and reading from an array.
Can you try it?

Sub CleanTimeString()
   Dim ws As Worksheet: Set ws = Sheets("Report")
   Dim i As Long
   Dim n As Long: n = ws.Range("J" & Rows.Count).End(xlUp).Row
   On Error Resume Next
   For i = 1 To n
      ws.Cells(i, 1).Value = Replace(ws.Cells(i, 1).Value, "T", " ")
   Next i
End Sub

Many thanks Pete! I tried it and it did not work. I did not receive any errors. It took about 2 minutes to run and when it was done, nothing happened.

 

[PeteWright]

Many thanks Pete! I tried it and it did not work. I did not receive any errors. It took about 2 minutes to run and when it was done, nothing happened.

Oopsie, my mistake. I tested it with Column A (instead of your Column J)

Here is the revised VBA code, could you please try it:

Sub CleanTimeString()
   Dim ws As Worksheet: Set ws = Sheets("Report")
   Dim i As Long
   Dim n As Long: n = ws.Range("J" & Rows.Count).End(xlUp).Row
   On Error Resume Next
   For i = 1 To n
      ws.Cells(i, 10).Value = Replace(Cells(i, 10).Value, "T", " ")
   Next i
End Sub

Edited @ 1:20 AM / Sorry, another typo (it's time to go to bed...)

 

[Jotoya]

Oopsie, my mistake. I tested it with Column A (instead of your Column J)

Here is the revised VBA code, could you please try it:

Sub CleanTimeString()
   Dim ws As Worksheet: Set ws = Sheets("Report")
   Dim i As Long
   Dim n As Long: n = ws.Range("J" & Rows.Count).End(xlUp).Row
   On Error Resume Next
   For i = 1 To n
      ws.Cells(i, 10).Value = Replace(Cells(i, 1).Value, "T", " ")
   Next i
End Sub

I myself didn't catch the cells(i, 1) too....facepalm. I'll give it a whirl....

 

[Jotoya]

Oopsie, my mistake. I tested it with Column A (instead of your Column J)

Here is the revised VBA code, could you please try it:

Sub CleanTimeString()
   Dim ws As Worksheet: Set ws = Sheets("Report")
   Dim i As Long
   Dim n As Long: n = ws.Range("J" & Rows.Count).End(xlUp).Row
   On Error Resume Next
   For i = 1 To n
      ws.Cells(i, 10).Value = Replace(Cells(i, 10).Value, "T", " ")
   Next i
End Sub

Edited @ 1:20 AM / Sorry, another typo (it's time to go to bed...)

It worked but it took over 3 minutes to complete unfortunately. I appreciate the solution Pete - many thanks! I'll need to find a routine that will be faster.

 

[Peter_SSs]

Application.Transpose certainly does have a limit (65,536). If you use it on data bigger than that you may get some very strange results and with no warning.
Try this on a blank worksheet. It puts 70,000 "1" values in column K 'Application.Transforms' them into an array and then same back into the next column.
M1 counts the numbers written back to the worksheet.
Inspect the results.
If you have used Application.Transfer on larger data in the past, you may not have the results that you were expecting even though the code may not have errored.

Sub Test()
  Dim arrK() As Variant
  
  Range("K1:K70000").Value = 1
  arrK = Application.Transpose(Range("K1:K70000"))
  Range("L1:L70000").Value = Application.Transpose(arrK)
  Range("M1").Formula = "=COUNT(L1:L70000)"
End Sub

With your large data I would definitely not be processing cell-by-cell on the worksheet, but you can just use a 2-dimensional array instead and the transform problem does not arise.
Try this with a copy of your data.

Sub Replace_T()
  Dim ArrJ As Variant
  Dim LastRowJ As Long, i As Long
  
  LastRowJ = Sheets("Report").Range("J" & Rows.Count).End(xlUp).Row
  ArrJ = Sheets("Report").Range("J3:J" & LastRowJ).Value
  For i = 1 To UBound(ArrJ)
    ArrJ(i, 1) = Replace(ArrJ(i, 1), "T", " ")
  Next i
  Sheets("Report").Range("J3:J" & LastRowJ).Value = ArrJ
End Sub

 

[Peter_SSs]

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