looping through and deleting row with certain values

[longhorn23]

Hi,

In my workbook, I have a certain values that I need to go through and delete (the values are in located in columnA). (I could do it manually, but I have to do it in several other .xls files as well, so I thought trying to create a macro would be more efficient.)

Please Login or Register  to view this content.

When I run the code, it just deletes EVERY row in my excel file.
I've also tried, separating them out into different If statements, and also taking out the "" from each number, but it still doesn't work.

[TMS]

I think this is your problem:

Selection.EntireRow.Delete ... specifically, "Selection"

The only place you select anything is:

Please Login or Register  to view this content.

So, every time a cell meets the criteria, it deletes Row 1 ... which rather confuses the issue because as soon as you start deleting rows, you're not checking the row you first thought of because it's moved up one.

If you delete rows like this, you need to delete them from the bottom up:

Please Login or Register  to view this content.

And change the delete to:

Please Login or Register  to view this content.

Regards

[longhorn23]

I think this is your problem:

Selection.EntireRow.Delete ... specifically, "Selection"

The only place you select anything is:

Thanks for the response!

I actually thought it was the same thing, but how would I fix it? Shouldn't it only go to the "selection.entirerow.delete" IF the value is equal to one of those numbers??

[TMS]

Sorry, pressed enter too soon. See the edited post.

The problem is that a cell will meet the condition but, when it does, it will always delete row 1.

Regards

[longhorn23]

Sorry, pressed enter too soon. See the edited post.

The problem is that a cell will meet the condition but, when it does, it will always delete row 1.

Regards

Interesting. Thanks for the input. I did change the code to:

Please Login or Register  to view this content.

And I tried to run it but it still didn't work. It deleted everything (all the rows).

[Marcol]

Try this

Please Login or Register  to view this content.

Hope this helps

[longhorn23]

Try this

Please Login or Register  to view this content.

Hope this helps

YES IT DID!!
Thanks soo much guys!

So is it better to use "cases" instead of "if" statements in situations like this?

[TMS]

I've just set up a test sheet and put a few random numbers in it. Stepping through the code does, as you say, delete every row regardless. The IF condition is TRUE whatever.

I'm guessing that it's all the ORs ... = 97 Or 489 Or 753 Or 767 and so on

I tend to use IF (x=97) OR (x=489) OR (x=753) OR (x=767), etc, so that the conditions are precisely defined. That said, there are a lot of conditions.

I'd be inclined to put all those conditions/numbers in a list on a separate sheet and then use COUNTIF to check if the value in cells(i,1) is there (COUNTIF(list, cells(i,1))>=1.

That would make the code shorter and easier to maintain.

Regards

[TMS]

Sorry, missed the post from Marcol.

Yes, the Select Case approach is better but I think I'd still take the conditions out of the code.

As it stands, if you want to add or delete a condition you have to edit the code.

Regards

[longhorn23]

Try this

Please Login or Register  to view this content.

Hope this helps

Hey so, I tried to run the code on another tab, in the same spreadsheet, but it doesn't wok. I don't even think it runs because it doesn't delete any values....

Okay it works, but I have a follow up question. Is there a way for this code to be run through the entire workbook?

[MarvinP]

Hey longhorn23,

Fun question. I cut your code into my VBA and stepped through it. Everything passed so all was deleted.

Down to the immediate window and tried some by hand and found that "? Cells(2,1) = "3" or "4" returned 4.

Astounding .... You need to put parens around all your numbers.

Try format like: cells(2,1)= ("3" or "4") as your test in the if statement.

[Marcol]

The code will run on whatever sheet is active, there should be no need to close and re-open the workbook.

Is it because you need to delete different values, as T M Shucks suggests?
I find it hard to believe that so many values could be common to several sheets, unless you are deleting lets say Archived/Closed projects

I also think that again as TM suggests, a lookup/helper column would be a good idea with so many values.

[longhorn23]

The code will run on whatever sheet is active, there should be no need to close and re-open the workbook.

Is it because you need to delete different values, as T M Shucks suggests?
I find it hard to believe that so many values could be common to several sheets, unless you are deleting lets say Archived/Closed projects

I also think that again as TM suggests, a lookup/helper column would be a good idea with so many values.

Yea it was the active sheet thing. hahaha (got overly anxious about this)
I just used the "Thisworkbook" in the Developers screen. And ran it from there and I think it worked. I still have to double check to make sure it did.

The [same/similar] values were in most of the sheets.

[JBeaucaire]

Looping through rows one by one testing for values is a common approach, but not always the fastest. Excel has an AutoFilter function designed to speed this up for you phenomenally.

A macro would be much faster if it:

1) Inserted a blank row at the top of the data
2) In an empty column put a formula that instantly tested all the rows for the possible values to delete
3) Turn on the AutoFilter in this new column and delete all the rows the formula flagged for you, all at once
4) Cleanup by turning off the AutoFilter, clearing the helper column and deleting that added row1

Something like this:

Please Login or Register  to view this content.

[longhorn23]

Hey longhorn23,

Fun question. I cut your code into my VBA and stepped through it. Everything passed so all was deleted.

Down to the immediate window and tried some by hand and found that "? Cells(2,1) = "3" or "4" returned 4.

Astounding .... You need to put parens around all your numbers.

Try format like: cells(2,1)= ("3" or "4") as your test in the if statement.

Hey MarvinP,

Adding the parens in the vba code doesn't seem to work either. Macrols code ended working though.

I find it weird/intriguing that my code didn't work, but I think TMShucks had a good point on why it didn't.

Thanks for the help though!

[Marcol]

The code should be in a standard module not in a sheet or ThisWorkbook module.

In the VBa editor
Insert > Module Paste all the code into the resultant pane.

Have a good look at JBs' code, it at first may seem a bit complex but it is a far better approach if your sheets contain a large ammount of data.

[MarvinP]

Hi longhorn23,

You were right that just adding parens didn't work. I hate to be wrong so here is a better answer. I think this is closer to what you wanted than all the above.

The following code will look for you numbers in Column A and delete the row if they are found in the DelArry(). I like this better than Jerry's as it is simpler.

Load the DelArry() numbers in the line of code using commas. Run the code and

Please Login or Register  to view this content.