VBA to delete a range of data based on a specified cell value

[nc_waggoner]

I am looking for a VBA code to delete the contents (not formatting) of a range of a row based on the cell value. For example, as seen below, I have number (col A), Name (col B), and Gender (col C). I would like to enter a number in Cell E2 (representing a number in column A) and have it delete the values of the Name and Gender columns. So if I type in 3 and click the button it will delete "Wayne" and "M".

Number	Name	Gender		Delete Number
1	Stone	M		?
2	Jordan	M
3	Wayne	M
4	Prince	F
5	Allen	M

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[alansidman]

Here is a VBA solution.

Option Explicit


Sub DelContents()
    Dim c As Range, rng As Range
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2:A" & lr)
    For Each c In rng
        If c = Range("D2") Then
            c.Offset(0, 1).ClearContents
            c.Offset(0, 2).ClearContents
        End If
    Next c
End Sub

 

[kalak]

Sub mgk()
Dim f
Set f = Range("A:A").Find([e2], LookIn:=xlValues, lookat:=xlWhole)
If Not f Is Nothing Then Rows(f.Row).Resize(, 2).Offset(, 1).ClearContents
End Sub