I am just starting out in working with the VBA in excel and was
wondering if anyone had some idea how I would go about doing this. I
want to be able to search a column for a specific match(I can do
this), then I want to select the row that the match is found in and
either clear it or delete it altogether. Please keep in mind that the
information for the row,column will have to be in variables before a
command is constructed to do the clearing or deletion. If anyone has
any idea on how to do this quickly, elegantly and efficiently, I would
appreciate some ideas on it. Thanks.

Something like this should work for you...

Sub FindandDeleteRow(TextToFind As String, ColumnToSearch As Variant, _
Optional SheetName As String = "")
Dim R As Range
On Error GoTo Whoops
If SheetName = "" Then SheetName = ActiveSheet.Name
With Worksheets(SheetName)
Set R = .Columns(ColumnToSearch).Find(TextToFind, , , xlWhole)
R.EntireRow.ClearContents
End With
Exit Sub
Whoops:
End Sub

You would call it from your own sub, function or macro passing in the
relevant arguments. For example, if you wanted to search for the entry
"FindMe" (without the quote marks) in Column F on the active sheet, then you
would have your procedure execute this line...

FindandDeleteRow "FindMe", "F"

You can optionally specify a specific sheet by name if you did not want to
execute the code against the active sheet. Note that the code just quietly
exits in the face of any errors, so you may want to make that part a little
more robust according to your own tastes. Also note that I made the search
look for a match of the entire contents of the cell... you can change this
by changing xlWhole to xlPart if you want to look for your search text
within the text of a cell.

--
Rick (MVP - Excel)


"yarrido" wrote in message
...
I am just starting out in working with the VBA in excel and was
wondering if anyone had some idea how I would go about doing this. I
want to be able to search a column for a specific match(I can do
this), then I want to select the row that the match is found in and
either clear it or delete it altogether. Please keep in mind that the
information for the row,column will have to be in variables before a
command is constructed to do the clearing or deletion. If anyone has
any idea on how to do this quickly, elegantly and efficiently, I would
appreciate some ideas on it. Thanks.

psuedo code:

Sub standard()
Dim myVar As String
myVar = "Something to find"
Set myRange = Sheets(1).Range("A2:A100")
For Each c In myRange
If c.Value = myVar Then
'Do something
End If
Next
End Sub

Looks for the phrase "Something to find" in each cell of Range A2:A100 and
if found it does whatever you put in place of 'Do Something. Could be:

c.EntireRow.Delete
c.ClearContents
c.EntireColumn.Hidden = True


"yarrido" wrote:

I am just starting out in working with the VBA in excel and was
wondering if anyone had some idea how I would go about doing this. I
want to be able to search a column for a specific match(I can do
this), then I want to select the row that the match is found in and
either clear it or delete it altogether. Please keep in mind that the
information for the row,column will have to be in variables before a
command is constructed to do the clearing or deletion. If anyone has
any idea on how to do this quickly, elegantly and efficiently, I would
appreciate some ideas on it. Thanks.