repeating macro

  • Thread starter Thread starter Roger R
  • Start date Start date
R

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
 
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
 
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
 
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,
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?
 
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
 
Back
Top