Combining find with clearcontents on multiple columns

  • Thread starter Thread starter RussB
  • Start date Start date
R

RussB

Sub clearZLRPIN()
'
' clearZLRPIN Macro
' Macro recorded 4/17/2003 by R Todd
'
Hi,

I am working on a macro that is close but not quite there. At various
points throughout the sheet, starting a row 1, column K contains
"ZLRPI". I'd like the macro to find all occurences in the spreadsheet
and each time clear columns K:M.

My code appears to stop after each occurence, and the occurrence at row
1 is the last to be cleared.

Can anyone offer constructive suggestions?

Thanks,
RussB

Sub ClearProgram
On Error GoTo Jump_Out

Range("A1").Select
Columns("K:K").Select
Selection.Find(What:="ZLRPI", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext _
, MatchCase:=True).Activate
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.ClearContents
ActiveCell.Select
Do
Selection.FindNext(After:=ActiveCell).Activate
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.ClearContents
Loop Until False
Jump_Out:
Range("A1").Select
End Sub
 
Something like:

Sub ClearZLRPI()
Dim c As Range
For Each c In Range("K4:K25")
If c.Value = "ZLRPI" Then c.Resize(1, 3).ClearContents
Next c
End Sub
 
Back
Top