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

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

On Sun, 13 Dec 2009 21:53:00 -0500, "Gary Keramidas"
<GKeramidasAtMSN.com wrote:

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

Gary,

Thanks for the quick reply! I'll play with this and see if I can learn
some more.



John Keith

On Sun, 13 Dec 2009 21:53:00 -0500, "Gary Keramidas"
<GKeramidasAtMSN.com wrote:

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,

I finally had time today to check your suggestion, it works as needed,
thanks.

Do I understand:

frow is the variable pointing to the first found row after the filter
is applied?

What does the Offset(1) do in the line where frow is defined?


John Keith