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.Delete
ActiveSheet.AutoFilterMode = False

Thanks for looking!



John Keith
(E-Mail Removed)

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(xlCellTypeVisible).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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.Delete
> ActiveSheet.AutoFilterMode = False
>
> Thanks for looking!
>
>
>
> John Keith
> (E-Mail Removed)

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
(E-Mail Removed)

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(xlCellTypeVisible).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
(E-Mail Removed)