For Loop returning memory error on large data set and I'm struggling to figure out why

[Orion19]

Hi! I'm refactoring code from a project I built when I was first learning. The program pulls data from a CSV report that takes a month's worth of data from the database. However, we now need to pull and process a year's worth. The current macro can handle a months worth of data (about 13,000 rows) but a years worth has a little over 160,000 rows (the CSV file is 251 MB). Here is the loop I'm trying to implement:

    Dim rng As Range, i As Long

    'I create a named range here because when I pull reports the amount of data is always different
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "NoClientList"
   
    Set rng = Range("NoClientList")
   
    For i = rng.Rows.Count To 1 Step -1
        If rng.Cells(i).Value = "No" Then rng.Cells(i).EntireRow.Delete
    Next

The error I get reads "There isn't enough memory to complete this action. Try using less data or closing other applications." Initially I was attempting to copy/paste the data to a new tab and figured the clipboard couldn't handle that much memory so that's when I switched to a loop. I had the i variable dimension set to integer at first but changed it to "Long" after realizing my mistake there. However, I'm still getting the same error. Is this too much for Excel to handle or am I missing something? TIA!

 

[Rick Rothstein]

I don't have a Mac so I don't know if this will work for you (it works on my PC)...

Sub DeleteRowsIfNoInColumnC()
  Application.ScreenUpdating = False
  On Error GoTo Whoops
  Columns("C").Replace "No", "#N/A", xlWhole, , False, , False, False
  Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
Whoops:
  Application.ScreenUpdating = True
End Sub

 

[diddi]

it may still be a CSV file. excel is interpreting the delimiter and importing without you intervening. so on the Mac you need to open the file from MSword this will stop the removal of the delimiter, that way we can see what it is. i suspect it will be a comma or a tab
Parsing is the act of inspecting / checking / filtering data for a particular purpose. every file is parsed on Open to see that it can be understood by excel (or word or any program when it loads its data files) we want to parse for the purpose of improving your program speed.

 

[Orion19]

it may still be a CSV file. excel is interpreting the delimiter and importing without you intervening. so on the Mac you need to open the file from MSword this will stop the removal of the delimiter, that way we can see what it is. i suspect it will be a comma or a tab
Parsing is the act of inspecting / checking / filtering data for a particular purpose. every file is parsed on Open to see that it can be understood by excel (or word or any program when it loads its data files) we want to parse for the purpose of improving your program speed.

That is really interesting! I had no idea that was happening behind the scenes. When I opened the file using Word it asked what kind of encoding I wanted. I selected MacOS as the default response and the file opened. Sure enough, the data is separated by commas. I'll try refactoring the code you sent so that I can use the file picker on a Mac with a comma as the delimiter. I'll get back to you ASAP. Thank you!

 

[diddi]

see if this works for you. you need to change to the mac dialog picker

Sub ParseCSV()
    Dim MyFile As String, DataLine As String, Temp, Col As Long
    MyFile = Application.FileDialog(msoFileDialogOpen)  '<----- convert to Mac
    ' minor updates
    Open MyFile For Input As #1
        While Not EOF(1)
            Line Input #1, DataLine
            If InStr(UCase(DataLine), "NO,") Then
                Temp = Split(DataLine, ",")
                For Col = 1 To UBound(Temp)
                    Cells(NewRow, Col) = Temp(Col)
                Next Col
            End If
        Wend
    Close
End Sub

 

[Orion19]

I don't have a Mac so I don't know if this will work for you (it works on my PC)...

Sub DeleteRowsIfNoInColumnC()
  Application.ScreenUpdating = False
  On Error GoTo Whoops
  Columns("C").Replace "No", "#N/A", xlWhole, , False, , False, False
  Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
Whoops:
  Application.ScreenUpdating = True
End Sub

Thank you! So far I've found that the code transfers between Mac and PC fairly well. I've only ran into trouble when the versions are outdated or when methods like file picker are called.

I tried running the sub but something odd happened. When I click "Run" the Macro window remains open and Excel stops responding to anything. I waited for 10 minutes hoping that the sub was attempting to run, but Excel doesn't respond at all. What's really strange is that usually the MacOS will identify when a program stops responding and allow you to Force Quit, but it doesn't list Excel as a program that isn't responding.

When I re-open the file it looks like part of it has changed, but it's difficult to tell if any part of the sub actually ran. I'm wondering if the file is just too much for Excel to handle.

 

[diddi]

i would run this in a workbook with empty worksheets first. that keeps your resource load low. if you can run it in a userform module, then place a single label on the userform called "Label1"
then
use this code

Sub Userform_Initialize()
    Dim MyFile As String, DataLine As String, Temp, Col As Long, NewRow As Long
    MyFile = Application.FileDialog(msoFileDialogOpen)
    ' minor updates
    Open MyFile For Input As #1
        While Not EOF(1)
            Line Input #1, DataLine
            If InStr(UCase(DataLine), "NO,") =0 Then
                NewRow = NewRow + 1
                Temp = Split(DataLine, ",")
                For Col = 1 To UBound(Temp)
                    Cells(NewRow, Col) = Temp(Col)
                Next Col
            End If
            If NewRow Mod 20 = 0 Then
                label1.Caption = "Found " & NewRow & " rows"
                DoEvents
            End If
        Wend
    Close
End Sub

 

[Orion19]

i would run this in a workbook with empty worksheets first. that keeps your resource load low. if you can run it in a userform module, then place a single label on the userform called "Label1"
then
use this code

Sub Userform_Initialize()
    Dim MyFile As String, DataLine As String, Temp, Col As Long, NewRow As Long
    MyFile = Application.FileDialog(msoFileDialogOpen)
    ' minor updates
    Open MyFile For Input As #1
        While Not EOF(1)
            Line Input #1, DataLine
            If InStr(UCase(DataLine), "NO,") =0 Then
                NewRow = NewRow + 1
                Temp = Split(DataLine, ",")
                For Col = 1 To UBound(Temp)
                    Cells(NewRow, Col) = Temp(Col)
                Next Col
            End If
            If NewRow Mod 20 = 0 Then
                label1.Caption = "Found " & NewRow & " rows"
                DoEvents
            End If
        Wend
    Close
End Sub

It looks like the file picker code I have only recognizes Word documents, so I'm working on fixing that now. Would you recommend I attempt the ParseCSV sub or Userform_Initialize sub first? I haven't used userforms before so I'll need to do a little self-education first.

 

[diddi]

it could reside in a module just as well, and is probably better suited to a module since there is no real user interface needed (unless there is more you have planned)
try this change:

MyFile = Application.FileDialog(msoFileDialogFilePicker)