i threw this togehter, didn't really test it, but give it a shot on some test
data
watch for wordwrap.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim frow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
With ws.Range("A1:M" & lastrow)
.AutoFilter field:=9, Criteria1:="Lbl"
frow =
..Columns(9).Offset(1).SpecialCells(xlCellTypeVisi ble).Cells.Row
lastrow = .Cells(Rows.Count, "I").End(xlUp).Row
End With
With ws.Range(Cells(frow, 9).Address & ":" & Cells(lastrow,
9).Address).SpecialCells(xlCellTypeVisible)
.Value = ""
End With
ws.AutoFilterMode = False
End Sub

--


Gary Keramidas
Excel 2003


"John Keith" wrote in message
...
I use the following code to search for the presence of a string
("Lbl") in column 9 of large array of data and then delete the rows
that match this criteria.

Rather than delete the rows I'd like to clear the contents of column 9
in each of the visible rows. Is there a way to modify this code to
accomplish that action?


Dim rTable As Range
Range("A1").Select
With Selection
If .Cells.Count 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
ActiveSheet.AutoFilterMode = False
rTable.AutoFilter Field:=9, Criteria1:="=*" & "Lbl" & "*", _
Operator:=xlAnd
rTable.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
ActiveSheet.AutoFilterMode = False

Thanks for looking!



John Keith