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!

 

[diddi]

is the "No" value in the CSV file you originally import

 

[Orion19]

is the "No" value in the CSV file you originally import

Yes that's the actual keyword I'm trying to find. If "No" is in column C I want to delete the row.

 

[diddi]

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]

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]

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]

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]

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]

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

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]

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

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!