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

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56
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:

VBA Code:
    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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,549
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I don't have a Mac so I don't know if this will work for you (it works on my PC)...
VBA Code:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 18, 2017
Messages
56
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
see if this works for you. you need to change to the mac dialog picker :)

VBA Code:
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

Board Regular
Joined
Dec 18, 2017
Messages
56

ADVERTISEMENT

I don't have a Mac so I don't know if this will work for you (it works on my PC)...
VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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
VBA 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

Board Regular
Joined
Dec 18, 2017
Messages
56
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
VBA 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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA Code:
MyFile = Application.FileDialog(msoFileDialogFilePicker)
 

Forum statistics

Threads
1,147,734
Messages
5,742,865
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top