Remove rows

[ron.bo]

I have the following code and am using excel 2003;

Sub RemoveRowByWord()
Dim myRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("2", "4")
'myStrings = Sheets("Sheet2").Range("A1:A3")
Set wks = ActiveSheet

With wks
Set myRng = .Range("a1: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

"On "sheet 1" "column A" I have 1,2,3,4... 25 in A1 to A25. On "sheet 2" "column A" I have 2,4,6,8,10... 24 in A1 to A12.

The above code works if I put the number/word into the array, but I do not want to put it into the code each time I add something to be deleted. Instead, I want to use a list on sheet 2 of rows to be deleted that I can continually add to.

I found a message from Ron de Bruin that stated "myStrings = Sheets("Sheet2").Range("A1:A250") could replace the array. However, that does not work for me.

So I am looking for the code that will delete rows on sheet 1 that have a value/string that is listed on sheet 2 row a.

Thanks for any help

Ronbo

 

[wigi]

myStrings = Application.Transpose(Sheets("Sheet2").Range("A1:A3").Value)

or

myStrings = Application.Transpose([Sheet2!A1:A3].Value)

Please add code tags to your code when you paste it on the forum. Thanks.

 

[ron.bo]

Perfect! Thank-you.

Ron.bo

P.S. I am somewhat new to this BB. Is there a way to mark a post as answered?

 

[wigi]

Perfect! Thank-you.

Ron.bo

P.S. I am somewhat new to this BB. Is there a way to mark a post as answered?

No, but you do not need to do it. A message like the above suffices and is gratefully acknowledged.