Once criteria has been met, delet the same row.

  • Thread starter Thread starter Les Stout
  • Start date Start date
L

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
 
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)
 
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
 
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.
 
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
 
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
 
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)
 
Back
Top