Once criteria has been met, delet the same row.

[Les Stout]

Good day,

I am Looping down the first column of a spreadsheet and checking for a
certain bit of text in the 3rd column, when i loop downwards and i find
what i am looking for, i want to delete the entire row with the data
that has met my requirements, can anybody help me with some code?

I also have multiple criteria that i must look for, is it possible to
use wild cards?

Example:
I have 10 different combinations, all starting with "H"


Do Until ActiveCell = ""
If ActiveCell.Offset(0, 2) = ("HEP") Then

ElseIf ActiveCell.Offset(0, 2) = ("HEPA") Then

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Don Guillett]

BTW You don't need the ()
If ActiveCell.Offset(, 2) = "HEP*" Then

 

[Nick Hodge]

Les

The following will find the last row in column A and then, working upward.
Delete any rows in which the entry in column C starts with h or H

Sub deleterows()
Dim lLastRow As Long
Dim x As Long

lLastRow = Range("A65536").End(xlUp).Row
For x = lLastRow To 1 Step -1
If UCase(Left(Range("A" & x).Offset(0, 2).Value, 1)) = "H" Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Les Stout]

Hi Nick/Don,

Thanks very much for the help, I really appreciate it.

Nick,

I also have the following and have tried to change the code to suit but
I cannot get it to work, Heeeelp ??

Like previously mentioned, I have1 10 starting with “H” and the rest
are, they must all be deleted;

AEPA, ASPA, AEPE, AEPR, ALPA, AZPA, AZPE ,LA ,LG , LU & NB.

I don’t think i understand the language, as I tried changing the code by
changing “H” to “*PA”, but it still did not work, has it something to do
with the for x ?

Can you use the wild card with as well?

As stated, I new to the game, but love it and very frustrated at this
stage, as I know what I want to do, but can’t !!


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Don Guillett]

Nick's code would delete the row IF the text in col C started with H or h.
If that is not what you want ask again.

 

[Tom Ogilvy]

Sub deleterows()
Dim lLastRow As Long
Dim x As Long
Dim arr as Variant
Dim i as long
arr = Array("AEPA", "ASPA", "AEPE", "AEPR", _
"ALPA", "AZPA", "AZPE" ,"LA" ,"LG" , "LU", "NB")
lLastRow = Range("A65536").End(xlUp).Row
For x = lLastRow To 1 Step -1
If UCase(Left(Range("A" & x).Offset(0, 2).Value, 1)) = "H" Then
Range("A" & x).EntireRow.Delete
Else
for i = lbound(arr) to ubound(arr)
if Instr(1,Range("A" & x).Offset(0,2).Value,arr(i),vbTextCompare) Then
Range("A" & x).EntireRow.Delete
Exit for
end if
Next
End If
Next x

End Sub

 

[Les Stout]

Hi Don,

as i treid to explain, i do want to delete the "H" parts, but the others
as listed in my last mail also

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Nick Hodge]

Tom

Thanks...that saved some of my brain cells. Hopefully it works for you Les

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)