LOL - Damned unqualified ranges - I assume you are calling or running this from
another sheet.

Try this:-

Sub FindRep()

Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long
Dim LastRow As Long

Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")

With Sht2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = .Range(.Cells(1, "A"), .Cells(LastRow, "A"))
End With

findme = Sht1.Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

With Rng
.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

End Sub


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"gregork" wrote in message
...
Hi Ken , Thanks for your reply. I've run into a hitch with the following
line:

Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))

I get the msg:" Run-time Error '1004'
Method 'Range' of object'_Worksheet failed

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.718 / Virus Database: 474 - Release Date: 09/07/2004