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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
is the "No" value in the CSV file you originally import
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
ok. if thats the case then you should parse the CSV on import. that way you dont have to do this CPU intensive, very slow step. can you post your CSV read sub
 

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56

ADVERTISEMENT

ok. if thats the case then you should parse the CSV on import. that way you dont have to do this CPU intensive, very slow step. can you post your CSV read sub
I tried to do a quick read on parsing but am not sure I fully understand it. The file isn't actually comma separated when I open it. It has the information already put into the right cell/column. Sorry, I'm not sure what you mean by "post your CSV read sub". Could you clarify?
 

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56
I tried to do a quick read on parsing but am not sure I fully understand it. The file isn't actually comma separated when I open it. It has the information already put into the right cell/column. Sorry, I'm not sure what you mean by "post your CSV read sub". Could you clarify?
I should specify that I get these files from a third party website so I'm not sure how much control I have over importing from them.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

how do you get the CSV into excel to work on it. if you just open it like a text file in excel, thats fine... can you provide the first couple of lines if the data file so i can see what it looks like
 

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56
how do you get the CSV into excel to work on it. if you just open it like a text file in excel, thats fine... can you provide the first couple of lines if the data file so i can see what it looks like
I download the file from a website we use to schedule clients. I enter a date range and it pulls the associated data. I couldn't post any of the data without modifying it significantly (HIPAA), but it opens in Excel as a document with 45 columns. The number of rows varies depending on the time length. The sample I pulled for a year's worth of data has about 161,000 rows and my brand new computer struggles to even open it. Thank you for all of your help! This has got me stumped.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
ok i have a trial version ready to try. 2 questions.
can the "no" appear in any column in the data (including the first and last)
i need to know what character is between the fields in a row of data. you said it is NOT a comma

VBA Code:
Sub ParseCSV()
    Dim MyFile As String, DataLine As String, Temp, Col as long
    MyFile = Application.FileDialog(msoFileDialogOpen)
    
    Open MyFile For Input As #1
        While Not EOF(1)
            Line Input #1, DataLine
            If InStr(UCase(DataLine), "NO") Then '  what columns can the "no" appear?
                Temp = Split(DataLine, ",") 'need to find out what the delimiter is
                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
ok i have a trial version ready to try. 2 questions.
can the "no" appear in any column in the data (including the first and last)
i need to know what character is between the fields in a row of data. you said it is NOT a comma

VBA Code:
Sub ParseCSV()
    Dim MyFile As String, DataLine As String, Temp, Col as long
    MyFile = Application.FileDialog(msoFileDialogOpen)
   
    Open MyFile For Input As #1
        While Not EOF(1)
            Line Input #1, DataLine
            If InStr(UCase(DataLine), "NO") Then '  what columns can the "no" appear?
                Temp = Split(DataLine, ",") 'need to find out what the delimiter is
                For Col = 1 To UBound(Temp)
                    Cells(NewRow, Col) = Temp(Col)
                Next Col
            End If
        Wend
    Close
End Sub
Sorry for the late reply. I took the weekend off with the family to try and get away for a minute lol. Here are the answers to your questions:

1) I'm hoping to search one specific column for the "No" then delete the entire row if "No" appears in that column. It might appear somewhere else in the data, but it's highly unlikely. I could also use the adjacent column as it contains a qualifier ("Client") that I could use as well.
2) It's a CSV, but it's already delimited (I think that's the same as parsing) when I download it. It opens just like a normal Excel file that is organized by rows and columns (i.e., row 1 has all the data headings and every subsequent row contains data that falls under that heading). In other words, the data isn't actually separated by commas in this particular file when I download it.

I should also mention that I'm running on MacOS and can't use the msoFileDialogOpen. However, I've managed to find a work around to that using mac script so I can still open a file picker. I can replace that code myself, but it looks like the rest of the code is attempting to parse the data before deleting anything. Am I reading that correctly? Thank you again for your help!
 

Forum statistics

Threads
1,144,236
Messages
5,723,173
Members
422,479
Latest member
Mr_Confused

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