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".


[TABLE="width: 349"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Name[/TD]
[TD]Gender[/TD]
[TD][/TD]
[TD]Delete Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Stone[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jordan[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Wayne[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Prince[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Allen[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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