Macro to find row and delete it

[drrafa]

Hello

I need a macro to run every time I press a button (form control).
It should read the cell value (number) in cell i3
then it should look in columm B3-B20 for that value
in case it finds it, it should delete that row (row a-g)
it should also create a new row so the total number of rows remains the same
in case it finds no match, it should do nothing.

Thx

 

[texasalynn]

Welcome to MrExcel board...

be sure to make a copy of your file before trying the macro.

Sub Delete_with_Value()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.DisplayAlerts = False
    
    x = Range("I3")
    Cells.AutoFilter Field:=2, Criteria1:=x
    Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).Select
    Range("B2:B" & LR).Delete
    Cells.AutoFilter
MsgBox "Rows with value of " & x & " have been deleted"
Application.DisplayAlerts = True
    
End Sub

 

[drrafa]

That was real fast.
I got a run time erro 1004 - autofilter method of range class failed.

Welcome to MrExcel board...

be sure to make a copy of your file before trying the macro.

Sub Delete_with_Value()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.DisplayAlerts = False
    
    x = Range("I3")
    Cells.AutoFilter Field:=2, Criteria1:=x
    Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).Select
    Range("B2:B" & LR).Delete
    Cells.AutoFilter
MsgBox "Rows with value of " & x & " have been deleted"
Application.DisplayAlerts = True
    
End Sub

 

[texasalynn]

post a sample of your data