Macro deleting rows based conditional on column data

[CDPRINT]

I need help with a macro that will delete rows based on the value "TRUE" in column I of a spreadsheet. I'm doing something wrong in idenitfying the range in the code and can't get it figured out. Any help would be appreciated.

[Andrew-R]

It should be fairly simple, but the problem might be that as you delete each row the size of your range changes, which could make a For ... Each ... Next loop throw a bit of a wobbler.

Why not post your code so far, so we can have a look?

[CDPRINT]

Here is the latest I am working on...

Please Login or Register  to view this content.

[Andrew-R]

Instead of Cells(900,"I") you need to use Cells(rwNum,"I"), otherwise it will loop until row 900 isn't equal to true and then go through the rest of the loop without doing anything.

[CDPRINT]

OK, I did that, but the macro is still not deleting the rows where the value "TRUE" is present in column "I". Could it be because I added the code to an existing macro instead . See a portion of the macro (it's really long, so only sending a bit) below..

Please Login or Register  to view this content.

[Andrew-R]

Where is lastrow defined?

Does column I contain "TRUE" or "True"? Your post and your comment both say "TRUE", but the code is looking for "True".

[CDPRINT]

I guess "Last Row" is not defined, but not sure how to do that. Currently the list has 900 rows, but data will be continuing to be added, so I need make sure that there is space for that. I changed the code to "TRUE" which is what the value actually is, but the rows with "TRUE are still not being deleted when running the macro. Below is the last portion of the macro that I am having trouble with.

Please Login or Register  to view this content.

[Marcol]

Try this

Please Login or Register  to view this content.

Hope ths helps.

[CDPRINT]

Great! That works. However, it takes a good while (about 3 minute) with the screen flickering to complete the macro. I am assuming this is normal..or is there something I can do to speed it up? Thanks!

[Andrew-R]

Before you start deleting rows add the line:

Please Login or Register  to view this content.

and after the delete rows routine add:

Please Login or Register  to view this content.

It will be a bit quicker, but won't appear to be doing anything while it works.

How many of the rows contain the value "TRUE"? If it's not that many then you could speed things up by using a Find to identify them, rather than looping through them iteratively. E.g.

Please Login or Register  to view this content.

[broro183]

hi,

The below link shows a few other approaches for deleting cells which are likely to be faster than looping through a cell at a time.

http://www.excelforum.com/excel-prog...ts-in-vba.html

hth
Rob

[CDPRINT]

I went to the link you suggested and I think deleting the rows on a filtered request where all of the rows are deleted at once would work well. I have tried to adapt the macro to my situation, but I can't get it to work..I am pretty new at writing macros. Can you help? The column I need to filter is "I". " I" only contains values either "TRUE" of "FALSE". I want to delete all the rows that contain "TRUE" as quickly as possible.

[CDPRINT]

Andrew...that worked great, I am trying one other way around that was recommended in a later post.to see if I can get it to go any faster. Thanks for your help!

[broro183]

hi,

Can you please upload a sample file (preferably with the code which you've attempted to modify)?
Ensure there is no confidential information in the sample file.

Thanks
Rob