Macro to delete rows with different data

[Guest]

I need a macro to find data in cell column 1, starting in cell A3 that
contains the data "D", "GL", "NO", "REPO" and "RUN". I need it to find these
cells, and if that cell contains any of the noted data above, to delete the
entire row. Can someone help me with this? I've tried using some of the
macros mentioned in this forum, but was only for one variable, and I wasn't
sure how to do it with multiple variables????

Any help appreciated!!!

 

[Ron de Bruin]

Try this for A3:A?

Change/add the names in the array
Array("jelle", "ron", "dave")

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 3
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Lrow = EndRow To StartRow Step -1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsError(Application.Match(.Cells(Lrow, "A").Value, _
Array("jelle", "ron", "dave"), 0)) Then .Rows(Lrow).Delete

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

 

[Dave Peterson]

One way:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("D", "GL", "NO", "REPO", "RUN")

Set wks = ActiveSheet

With wks
Set myRng = .Range("a3:a" & .Rows.Count)
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With myRng
Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub

 

[Ron de Bruin]

Here is another one from Dave that use find with a inputbox
http://www.rondebruin.nl/delete.htm#Find

 

[Dave Peterson]

Hey, that looks familiar!

Here is another one from Dave that use find with a inputbox
http://www.rondebruin.nl/delete.htm#Find

 

[Guest]

Worked like a charm....thank you so much!!!!

One way:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("D", "GL", "NO", "REPO", "RUN")

Set wks = ActiveSheet

With wks
Set myRng = .Range("a3:a" & .Rows.Count)
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With myRng
Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub

 

[Guest]

ok, now I have a different question, but along the same lines. I have
another report that has the same data as before, ("D", "GL", "NO", "REPO" and
"RUN"), however in addition to that, have some others, such as "* DE", "* FI"
and some that are blank. I toyed with this trying to get it to remove the
rows as well, that contained this data, the problem now is some of it goes
through to column K, for instance the "* DE" may show up in Column K as well
as A. How would I do that?....wondering if the * is what was causing me to
not be able to do this, but it is part of the text field....also got stumped
on the blank cells.....If Cells in column A are blank, would need to get rid
of them as well, they would be followed by garbage in the cells in that row.
I spent about 6 hrs yesterday working on this to no avail.....help
please!!!???? Thanks in advance for all your terrific advice, you guys have
been a godsend to me!!!

 

[Dave Peterson]

The asterisk (*) represents a wildcard and excel's find will use that asterisk
as a wildcard.

But if you want the actual character, you can use ~* to really find the
asterisk.

~* to find *
~? to find ?
~~ to find ~

(The tilde (~) is the "escape" character that tells excel that you really want
to find that next character--not to use it as a wildcard.

So you could change what you're looking for:

myStrings = Array("* DE", "* FI")
to
myStrings = Array("~* DE", "~* FI")

or you could have your code do it:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long
Dim myRealStr As String

myStrings = Array("* DE", "* FI")

Set wks = ActiveSheet

With wks
'this line changed, too!
'columns A and K, not A:K, right?
Set myRng = Union(.Range("a3:a" & .Rows.Count), _
.Range("k3:k" & .Rows.Count))
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With myRng
myRealStr = Replace(myStrings(iCtr), "~", "~~")
myRealStr = Replace(myRealStr, "*", "~*")
myRealStr = Replace(myRealStr, "?", "~?")

Set FoundCell = .Cells.Find(what:=myRealStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub

 

[Guest]

wow, thanks Dave, I didn't know that....I will try it as soon as I get my
morning reports out. Is there a way I can get rid of the rows that have
blanks in column A?

 

[Dave Peterson]

Are the cells in column A really empty or do they contain formulas that look
empty?

If they're really empty:

on error resume next 'just in case
worksheets("somesheetnamehere").columns(1) _
.cells.specialcells(xlcelltypeblanks).entirerow.delete

wow, thanks Dave, I didn't know that....I will try it as soon as I get my
morning reports out. Is there a way I can get rid of the rows that have
blanks in column A?

 

[Ron de Bruin]

If you have a lot of data maybe you hit the limit ?

See this page for a example that count the areas
http://www.rondebruin.nl/specialcells.htm