VBA to delete Product item row from data entry sheet

[pateltarunt]

I have my product item data table in Sheet.2 that contain my product item number-name-sales data etc.

I have create another sheet.1 for getting different search result from main data sheet table in Sheet.2

I want to delete Product item based on item number. For that If I enter Item number in Sheet.1 and hit delete button this command go into sheet.2 (Data entry sheet table) Item number column and search for that item then select complete row and then delete it and come back to my Sheet.1.

I have create the below macro but it will not select the Product item row and hence delete of item is not perform.

>>> use code - tags <<<

Dim abc As String
    abc = Sheets("Sheet.1").Range("G5")
    Sheets("Sheet.2").Select
    Set Rng = Columns("A:A").Find(What:=abc, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    rownumber = Rng.Row
    ActiveCell.EntireRow.Delete Shift:=xlUp
    Range("A2").Select
    Sheets("Sheet.1").Select
    Range("G5").Select

Please check it and give correct code.

 

[p45cal]

Only two lines needed for all that:

Set Rng = Sheets("Sheet.2").Columns("A:A").Find(What:=CStr(Sheets("Sheet.1").Range("G5")), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then Rng.EntireRow.Delete

 

[pateltarunt]

Hi P45cal
Your code works fine.
Thanks for solution.

 

[Marc L]

A VBA demonstration using worksheets codename rather than name :​

    With Sheet2.UsedRange
        V = Application.Match(Sheet1.[G5].Value2, .Columns(1), 0):  If IsNumeric(V) Then .Rows(V).Delete
    End With

Do you like it ? So thanks to click on bottom right Like !​

​