Delete row on different worksheets from an input value vba

Question

Cannot understand why my delete macro is not finding my input value within the multiple other worksheets within my workbook.

Tried to create a loop that selects the active workbook, look through "B" column (workbook has same column for input values) for the matching value, and delete the cells row, then select the next worksheet and repeat.

Sub delete_record_btn_Click()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim lRow As Long
Dim IDRef As String

IDRef = InputBox("Please enter the selected ID.")
If IDRef = vbNullString Then Exit Sub


For Each ws In ActiveWorkbook.Worksheets
ws.Select

lRow = Range("B" & Rows.Count).End(xlUp).Row

For Each Cell In Range("B2:B" & lRow)
    If Cell = IDRef Then
       Cell.EntireRow.Delete
    End If
Next Cell
Next ws

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

Delete the corresponding record row on all three worksheets. Mass deleting rows based on an input value.

It's best to delete from the bottom up. Also, see How to avoid using Select. — BigBen

Tim Williams Tim Williams · Accepted Answer · 2019-05-09T03:03:15

Most things can be done without selecting/activating, and as BigBen points out a bottom-up approach is best when deleting rows.

Sub delete_record_btn_Click()

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim i as long
    Dim IDRef As String

    IDRef = InputBox("Please enter the selected ID.")
    If IDRef = vbNullString Then Exit Sub

    For Each ws In ActiveWorkbook.Worksheets
        For i = ws.Range("B" & ws.Rows.Count).End(xlUp).Row to 2 step -1

            With ws.Cells(i,"B")
                If .Value = IDRef Then .EntireRow.Delete
            End with

        Next i
    Next ws

    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

Delete row on different worksheets from an input value vba

1 Answers