How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

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

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
...
I have been searching through google groups to try and find a previous post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but when I
modify it for a cell value it fails.

greg






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