I have to remove large chunks of information from tables in .xlsx files using Excel 2010. Every method I have tried so far has produced no results, either crashed or I've killed it after waiting for a day.

Somebody else created the spreadsheets using an SQL server. I have to analyse the data, but in order to do that I have to remove all of the bad data and then visualise it. I have asked the server guy to filter on his end but he says only one person in the company is competent to do that and they're too busy. I can't comment on that because I don't know anything about databases.

There are approximately ~500,000 records in the typical spreadsheet. I have tried removing all of the bad values manually using a filter, but my PC crashes when I delete of the bad records.

I have turned the .xlsx files into .csv files because I think these are simpler, and although they seem faster, they still crash.

I've written a VBA script, which I've tried to leave running for several days to no avail:

Sub delete_bad_records()
Dim not_good() As Variant
Dim cell As Excel.range
Dim none As Boolean

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

not_good = Array("example_value", "another one")
none = False

For Each element In not_good
    none = False
    Do While Not none
        Set cell = Selection.Find(element, ActiveCell)
        If cell Is Nothing Then
            none = True
        End If
Next element

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

And I have also written a .bat file using findstr as a kind of filter, however some records mysteriously disappear and I'm not sure why. The general format is:

findstr /v "keywords" "original.csv" > "filtered1.csv"
findstr /v /l "specific phrase1" "filtered1.csv" > "filtered2.csv"
findstr /v /l "specific phrase2" "filtered2.csv" > "filtered1.csv"
  • If you can actually load the file fine in Excel, save it as .csv, then open the .csv file in something like Notepad++
    – LPChip
    Jun 25, 2018 at 12:57
  • 1
    Personally I would use Powershell on the CSV. Something like Import-CSV c:\Path\To\File.csv | ?{$arrayOfIlters -notContains $_.ColumnHeader} | Export-CSV c:\path\To\NewFile.csv -NoTypeInformation
    – EBGreen
    Jun 25, 2018 at 13:05
  • It's a work machine and it would be very complicated to install another program like Notepad++. What would you suggest I do once I've opened it in Notepad++?
    – scc268
    Jun 25, 2018 at 13:06
  • I bet Powershell is already on it. :)
    – EBGreen
    Jun 25, 2018 at 13:13
  • 1
    @scc268 - You can use a text file to list the filter values then do something like $arrayOfFilters = Get-Gontent c:\path\to\filters.txt
    – EBGreen
    Jun 25, 2018 at 14:37

2 Answers 2


Thanks to @EBGreen for suggesting PowerShell and showing me how to use it; although it seems simple now, I would have never known/thought about using PowerShell on my own!

Here's what I did:

  1. Create the .csv file
  2. Add a filter in Excel, and remove all the entries with the type I do want
  3. Copy the filtered list of entries with types I do not want and then remove duplicates
  4. Save this in not_good.txt
  5. Run this PowerShell script:

    $not_good = Get-Content .\not_good.txt
    Import-CSV ".\results.csv"  | ?{$not_good -notContains $_.Type} | Export-CSV ".\results filtered.csv" -NoTypeInformation

The script takes seconds, and I can then use it for all of the other spreadsheets I've got.


If you want to load a lot of data into Excel, use Microsoft's Get & Transform power tools. (For Excel 2010-2013, use the free MS add-in Power Query for Excel.)

With this tool you have the possibility to access many different sources (CSV, Excel files, database, web, ...) without the need to write code. Also, transforming your data or combining it with other sources is quite easy.

  • Cheers for letting me know about these tools. I wasn't aware that they existed.
    – scc268
    Jun 27, 2018 at 13:47
  • you're welcome. These quite recent tools of Excel (Power Query, Power Pivot) will definitely bring you to the next level as power user.
    – visu-l
    Jun 27, 2018 at 14:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .