repeating macro

[Roger R]

I have recorded a macro that does a simple task: It finds
a certain group of characters, then deletes the entire row
where these exsist. I can't however seem to make it
repeat over and over again until it has deleted every row
that hold these charaters. Hope someone can help.

Roger

 

[Frank Kabel]

Hi Roger
you may post your existing coe
General ruile: You have to loop through the loops starting in the LAST
row going upwards. So try something like the following:

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "Q").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "A")
if .value = "ABC" then
Rows(RowNdx).Delete
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

 

[Guest]

Frank,
That's a mouth full..
Here is what I recorded, but I think your's is the winner!!
the VBA you listed will look for "ABC" in the entire row,
right?

Cells.Find(What:="XXXX", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False).Activate
Rows("1104:1104").Select
Range("B1104").Activate
Selection.Delete Shift:=xlUp

 

[Gord Dibben]

Roger

Sub DeleteRows_With_Text()
findstring = "mystring"
Set b = Range("C:C").Find(What:=findstring, LookAt:=xlPart)
While Not (b Is Nothing)
b.EntireRow.Delete
Set b = Range("C:C").Find(What:=findstring, LookAt:=xlPart)
Wend
End Sub

Change "mystring" to your group of characters.

Change C:C to your column(s)

Gord Dibben Excel MVP

 

[Frank Kabel]

Hi
my code only looks in column A for this entry

 

[Roger R]

Frank,
I gave it a go with your code, upon replacing column A w/
B, but it still didn't work. What does the "Q" do?

 

[Frank Kabel]

Hi
sorry, missed that bit while changing the macro. Try
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "B")
if .value = "ABC" then
Rows(RowNdx).Delete
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub