Deleting rows which meet a specific criterion

Sorry if this is simplistic, but...

Is there a macro command to execute an "if"-function-like check to do something like deleting all rows if and only if a vale is in a column?

Alan:

Thanks for your help.

2 things. This Compile Error popped up when I tried to run the macro:

Attachment 835413


2) I have specific (though I'm sure you'd say simplistic) questions about what you wrote, but only born from my own ignorance and rust (from not using VBA recently enough). If I posted my questions might you have time to look at them?

Jules

Alan:

That did work, thanks. I want to be sure I understand what it's doing, so I've posted my version of the narrative of what's happening below. Let me know if I am way off.

Also, not in the narrative below, but if I also wanted to delete, say the "Genderqueer" entries, would I duplicate a particular command and change the crit? if so which lines?

1) Option Explicit

2) Sub DelX()
3) Dim i As Long, lr As Long
4) Dim crit As String
5) Application.ScreenUpdating = False
6) lr = Range("A" & Rows.Count).End(xlUp).Row
7) crit = "Female"
8) For i = lr To 2 Step -1
9) If Range("E" & i) = crit Then
10) Range("E" & i).EntireRow.Delete
11) End If
12) Next i
13) Application.ScreenUpdating = True
14) MsgBox "Action Completed"

15) End Sub

Lines 1) and 2) are obviously names. Lines 3 and 4 contain the Dim command (from dimension), but are the parts after those Name Definitions or a separate command? What are the “i” and “lr” and “Long”?
Line 5 freezes the screen while running.

Line 6 This seems to be defining “lr” as a range (which I think says it is column “A” and the number of rows is defined by the “Rows.Count” command?) End(xlUp) is the command for Shift+End+Up to get to cove the full range or a blank or the top, right? What does the.’Row’ after the period at the end do?

Line 7 I think crit = “Female” sets the criterion to Female,

Line 8 is setting up the repetitive if/then statement, but, again, I’m not sure what “i” and “lr” are. I think it’s specifying how many times to repeat the action, and that Step -1 is either a part of that, or an additional command, but if you could briefly walk me through that it would help.

Line 9 is saying that if Column E and “i” match the crit (criteria, I assume?), then [execute] Line 10, delete the entire row. Line 11 ends the imbedded If, and Line 12 ends the For Statement.

Line 13 turns the screen back on, and Line 14 posts a message telling you it’s done.

So I followed what it’s doing, just not the details of how or all of the syntax. Your help is greatly appreciated.
 

Thanks for the alternative. I'll try to digest this and revert with questions. Thanks again

Jules

sintek:

Thanks, that code also worked. I will try to work through the commands in an effort to decipher the steps in my brain, but I appreciate you effort. Marking post as solved and bumping your "Rep".

Jules

This was very helpful, thanks! I was able to figure out which part to duplicate to delete more than one criteria, so I was able to apply it to the project on which I was stuck. Bumping you "Rep" again, and making the thread solved.

Thanks again for this help. I thought I understood, but I tried to just replicate the part of your macro, changing the Const Crit = "Female" part, but in trying to replicate and change the Const Crit part to delete a second entry in the same column I got a "Compile Error: Duplicate Declaration in Current Scope" error. Shouldn't I just be able to do the same instruction set with a different variable? Any help would be appreciated.

One other thing that occurred to me would be to have your instrux with different letters ("W", "L", "P") in separate macros and invoke them within the original.