Efficient VBA code for conditionally deleting rows in big excel sheet

Please Login or Register  to view this content.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Originally Posted by akash27

Shows run-time error '13': Type mismatch
& did you get 8 sec completion time on the 40 MB excel file which I have shared on google drive?

works for me on the 40MB CSV file from the google drive link. are you copying this code into a new module and then running?

yes, it takes 8 secs to complete for the 40MB file

Value for money - there's at least 20 people replying to this on 4 different forums. So much for forum "rules" about adding links.

No, I'm running the code in Sheet 1 only.

Originally Posted by thatandyward

works for me on the 40MB CSV file from the google drive link. are you copying this code into a new module and then running?

yes, it takes 8 secs to complete for the 40MB file

Thanks! this is the fastest method (perhaps because Windows allow PowerShell to use all cores, whereas multithreading is not available in VB)

Originally Posted by thatandyward

Jindon - I like this approach, much better/faster to process as a text file; however I think your RegEx pattern is incorrect, this leaves lines where NA is only in the last column i.e. there is no trailing comma. the pattern "^.*NA,.*?\r\n|^.*,NA\r\n" would remove all occurrences of NA including those only in the last column.

this approach also got me thinking about how else to achieve the goal, as this is a CSV file, parsing as text at the command line is likely the most efficient way; Jindon's code effectively does this, just from within Excel.

if you're on a Unix machine (Linux or OS X) then you can use SED (stream editor) from the terminal. this processes the changes in under a second!

sed '/NA/d' ahs-comb-madhya_pradesh-dhar.csv > ahs-comb-madhya_pradesh-dhar_CLEAN.csv

I believe you could use PowerShell on Windows to do something similar but am not very familiar with it, the below command appears to work, although takes about 7 seconds, so not as fast as Jindon's code.

Get-Content .\ahs-comb-madhya_pradesh-dhar.csv | Where-Object {$_ -CNotMatch 'NA'} | Set-Content ahs-comb-madhya_pradesh-dhar_CLEAN.csv

causes Excel to crash (even on Xenon machines)

Originally Posted by xladept

Did you try my routine? What happened??

Originally Posted by akash27

Thanks! this is the fastest method (perhaps because Windows allow PowerShell to use all cores, whereas multithreading is not available in VB)

glad it worked.

performing operations in memory is always faster than inside an instance of excel as there is less overhead to deal with. my earlier code achieves similar performance as it loads the data into an array and processes it in memory then writes it out to the sheet all at once.

Jindon's code also manipulates the data in memory, his code is even faster (ran at about 5 sec on my machine)

using SED on a Unix machine is particularly impressive as it can process the changes in under a second!

Same error Type mismatch

Originally Posted by thatandyward

it should still work in the Sheet1 module; have you tried running it with a 'fresh' file?

I have just re-verified the code works with the original download. these are the steps I took.

1. download CSV from the link you provided
2. open CSV in excel
3. go to developer tab & click Visual Basic button
4. open up the Sheet1 code module
5. paste my code into sheet1 code module
6. close Visual Basic editor
7. click Macro button
8. select Sheet1.DeleteNAs and click run

the code then successfully runs without any errors.

In the same CSV file if I want to delete all the entries for age<18. The fastest VB code would first sort the "age" column, delete all the rows till it hits 18 and then shift the cells upward.
@thatandyward In your opinion, could this compare and delete operation be done in a faster way through PowerShell?

Originally Posted by thatandyward

Jindon - I like this approach, much better/faster to process as a text file; however I think your RegEx pattern is incorrect, this leaves lines where NA is only in the last column i.e. there is no trailing comma. the pattern "^.*NA,.*?\r\n|^.*,NA\r\n" would remove all occurrences of NA including those only in the last column.

this approach also got me thinking about how else to achieve the goal, as this is a CSV file, parsing as text at the command line is likely the most efficient way; Jindon's code effectively does this, just from within Excel.

if you're on a Unix machine (Linux or OS X) then you can use SED (stream editor) from the terminal. this processes the changes in under a second!

sed '/NA/d' ahs-comb-madhya_pradesh-dhar.csv > ahs-comb-madhya_pradesh-dhar_CLEAN.csv

I believe you could use PowerShell on Windows to do something similar but am not very familiar with it, the below command appears to work, although takes about 7 seconds, so not as fast as Jindon's code.

Get-Content .\ahs-comb-madhya_pradesh-dhar.csv | Where-Object {$_ -CNotMatch 'NA'} | Set-Content ahs-comb-madhya_pradesh-dhar_CLEAN.csv

it's always going to be faster to perform the operations in memory rather than using excel functions.

it is possible to do this with Powershell, similar to how we removed all the lines containing 'NA', we just need a slightly more advanced RegEx statement.

^(?:[^,]*\,){4}([0-9],|1[0-7]) will match all lines where the digits after the 4th comma are less than 18, so we can put that into the same Where-Object -CNotMatch statement

Get-Content .\ahs-comb-madhya_pradesh-dhar_CLEAN.csv | Where-Object {$_ -CNotMatch '^(?:[^,]*\,){4}([0-9],|1[0-7])'} | Set-Content ahs-comb-madhya_pradesh-dhar_CLEAN_OVR18.csv