why is range empty?

[JT]

In the code below, rngToFind is being filled correctly.
The problem is that rngFound is always empty. On sheet
("A"), there is data in column 1. Some items will match
and some won't. If the item (from sheet "P") is not found
in the rngFound (from sheet "A") then the item on
sheeet "P" should be deleted.

This is not happening. When I put my coursor over
rngFound it says empty.

Any suggestions or help would be appreciated. Thanks....

Dim rngToFind As Range
Dim rngFound As Range

Set rngToFind = Sheets("P").Range("A" & lstrowPP).End(xlUp)

Do While rngToFind.Row > 1

Set rngFound = Sheets("A").Columns(1).Find rngToFind.Value)

Set rngToFind = rngToFind.Offset(-1, 0)

If Not rngFound Is Nothing Then
rngToFind.Offset(1, 0).EntireRow.Delete

End If
Loop

 

[Jim Rech]

This works for me. Adjust the Find arguments to do the specific type of
Find you want. See VBA help for more on Find.

Sub FindOrDelete()
Dim Cell As Range
Dim PRg As Range
Dim DeleteRg As Range
With Worksheets("P")
Set PRg = .Range("A2", .Range("A2").End(xlDown))
End With
For Each Cell In PRg
If Worksheets("A").Columns(1) _
.Find(Cell.Value, , xlValues, xlWhole) Is Nothing Then
If DeleteRg Is Nothing Then
Set DeleteRg = Cell
Else
Set DeleteRg = Union(DeleteRg, Cell)
End If
End If
Next
If Not DeleteRg Is Nothing Then
DeleteRg.EntireRow.Delete
End If
End Sub


--
Jim Rech
Excel MVP
| In the code below, rngToFind is being filled correctly.
| The problem is that rngFound is always empty. On sheet
| ("A"), there is data in column 1. Some items will match
| and some won't. If the item (from sheet "P") is not found
| in the rngFound (from sheet "A") then the item on
| sheeet "P" should be deleted.
|
| This is not happening. When I put my coursor over
| rngFound it says empty.
|
| Any suggestions or help would be appreciated. Thanks....
|
| Dim rngToFind As Range
| Dim rngFound As Range
|
| Set rngToFind = Sheets("P").Range("A" & lstrowPP).End(xlUp)
|
| Do While rngToFind.Row > 1
|
| Set rngFound = Sheets("A").Columns(1).Find rngToFind.Value)
|
| Set rngToFind = rngToFind.Offset(-1, 0)
|
| If Not rngFound Is Nothing Then
| rngToFind.Offset(1, 0).EntireRow.Delete
|
| End If
| Loop
|