VBA - finding and deleting specific cells

[Puppies72]

Hi all,

I have a macro that imports data from several sheets into a master sheet however I need to clear certain data from the master sheet prior to the import.

I need a bit of code that will do the following:

Search K4:K10003 for any cells where the value = 16 , once a cell is found then clear that cells' contents and the contents of the cell to the left, then find the next one and repeat

I don't want the cells deleted just the contents cleared.

As always help greatly appreciated

 

[cb12]

Perhaps something like

Sub delete_sixteens()

Dim i As Integer
    Range("K4").Select
 For i = 1 To Selection.CurrentRegion.Rows.Count - 1
    If ActiveCell.Value = 16 Then
        ActiveCell.ClearContents
        ActiveCell.Offset(0, -1).ClearContents
        ActiveCell.Offset(1, 0).Select
    Else: ActiveCell.Offset(1, 0).Select
    End If
 Next i
End Sub

might be of use. To edit you could always replace 16 with a variable that is dependent on a cell's value (for example) as per your needs. Similarly I've assumed all rows between 4 and 10003 are filled but if not you can substitute the loop accordingly.

HTH

 

[Puppies72]

thanks muchly I'll give that a go in the morning and let you know how I get on