entirerow.delete multiple text conditions

[Jocelyn]

Hi,

Total newbie question here. I'd like some help with deleting entire
rows based on multiple text conditions. In hunting around on the
group, I snagged this macro, which will work for one text condition.
How can I change it to make it select all cells on the sheet named
Sheet2, look for multiple conditions and delete rows based on all of
them in a single macro?

Sub deleteWithI()
For Each myCell In Selection
If myCell = "I" Then myCell.EntireRow.Delete
Next myCell
End Sub

TIA,
Jocelyn

 

[Tom Ogilvy]

Sub deleteWithMultiple()
Dim rng as Range, mycell as Range
For Each myCell In Selection
if mycell = "I" or mycell = "J" or mycell = "B" or _
mycell = "T" then
if rng is nothing then
set rng = mycell
else
set rng = Union(rng,mycell)
end if
End if
Next myCell
if not mycell is nothing then _
mycell.EntireRow.Delete
End Sub

 

[Jocelyn]

Hi Tom -
For some reason, this didn't work for me. I pasted the code into a
module and replaced your I, J, B, & T with some of the elements I
wanted to remove. I selected all on the active worksheet and ran this
and got an Compile Error: End If without Block If. I inserted a line
before End Sub and put in an End If there, but it still didn't remove
the elements. Any thoughts?

Thanks,
Jocelyn

 

[Tom Ogilvy]

As written, it compiles fine for me - so you must have screwed it up in some
way when you made your changes. Perhaps if you post what you did, it can
be corrected.

 

[Jocelyn]

Tom,

To simplify matters, I opened a brand-new workbook this morning, put
the letters A to Z in Column A, and pasted the code you provided,
exactly as written, in a new module. I returned to the workbook and
ran the macro, and after I ran it, the I, J, B, and T were still
there. I went back to the code and deleted the underscores, bringing
those two long lines onto a single line, and tried again. Still no
dice. It works for you, though? Any thoughts about what else I could
be doing wrong in what seems like a pretty straightforward experiment?

Thanks a million for your help and patience.

Jocelyn