Delete Specific Data from an Excel Worksheet Using VBA

My sheet1 has the following records in Excel 2007:
Code      Item      dim      qty      price
1001      Ball         7″        1         100
1002      Belt         36″      1         150
1003      Chain      12″      1           50


Now in sheet2 in any of the cell, say from A1 to A10 if I mention just 1001 and enter then in sheet1 the entire row from Item to Price should become blank. But the number in cell should not be deleted (i.e 1001) Now in sheet2 again in cell A7 I give 1003 then in sheet1 the entire row from Item to Price should become blank. But the number in cell should not be deleted (i.e 1007) Looking out for the solution for this. Tried a lot but unable to do it.

The user wants to enter a code number in sheet2 and automatically delete the data related with that code from sheet1 on pressing enter. When we wish to do some action by pressing a key, we generally could use a selection_change event in the worksheet. However, since we wish to access the sheet two we have to leave sheet1 and therefore the code ‘selection-chnage’ trigger may not work due to its private nature. We need to write a stand-alone macro to perform the action. The VBA code for the macro is given below:
Sub deletespecificdata()
Dim num As Integer
Sheets(“sheet2”).Select
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
num = Cells(erow – 1, 1).Value
Sheets(“Sheet1”).Select
Dim row As Long
row = 2
Do While Cells(row, 1) <> “”
If Cells(row, 1) = num Then
Range(Cells(row, 2), Cells(row, 5)).Select
Range(Cells(row, 2), Cells(row, 5)).ClearContents
End If
row = row + 1
Loop
End Sub

Note: You can assign a shortcut like ‘ctrl+m’ to be able to run the macro using these combination keys.

Watch the video:


2 thoughts on “Delete Specific Data from an Excel Worksheet Using VBA

  1. Subodh Tiwari

    The following piece of code for Worksheet_Change Event on Sheet2 module will do the trick.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim wsrng As Range
    Dim r As Long
    Set ws = Worksheets(“Sheet1”)
    Set wsrng = ws.Range(“A:A”)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“A:A”)) Is Nothing Then
    On Error Resume Next
    r = Application.Match(Target, ws1rng, 0)
    If r > 0 Then
    ws1.Range(“B” & r & “:E” & r).ClearContents
    End If
    End If
    End Sub

    What to you say?

    Reply
  2. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

Leave a Reply

Your email address will not be published. Required fields are marked *