Code modification

[Todd Huttenstine]

Because of some unknown code problem in my other code I am
trying to write another to do the same thing. What this
code does is looks in cell C2 and then looks in range
B1:B200 to try and find a match. If it finds a match it
deletes it. How do I make this loop?

Dim rngJ As Range
Set rngJ = Workbooks("Book2.xls").Sheets
("Resolutions").Range("B1:B200").Find(Workbooks
("Book2.xls").Sheets("Resolutions").Range("C2")) 'where
lookup value

If Not rngJ Is Nothing Then
rngJ.Resize(, 1).Delete Shift:=xlShiftUp
Next rngJ
End If

 

[Rob van Gelder]

Sub test()
Dim rngJ As Range

With Workbooks("Book2.xls").Sheets("Resolutions")
Set rngJ = .Range("B1:B200").Find(.Range("C2"))
Do Until rngJ Is Nothing
rngJ.Resize(, 1).Delete Shift:=xlShiftUp
Set rngJ = .Range("B1:B200").FindNext
Loop
End With
End Sub

 

[Bob Phillips]

With ActiveWorkbook.Sheets("Resolutions")
'Workbooks("Book2.xls").Sheets("Resolutions")
Set rngJ = .Range("B1:B200").Find(.Range("C2"))

If Not rngJ Is Nothing Then
rngJ.EntireRow.Delete Shift:=xlShiftUp
fValid = True
Do
Set rngJ = .Range("B1:B200").Find(.Range("C2"))
If Not rngJ Is Nothing Then
rngJ.EntireRow.Delete Shift:=xlShiftUp
Else
fValid = False
End If
Loop Until Not fValid
End If
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)