Deleting row if cell equals dynamic value in entire workbook - VBA

[skghkjlssgh]

So I've got a cell where an item code can be entered in, and then once the button is clicked I want every row that contains that item code to be deleted. The item code is in different columns in different tabs and it needs to be able to run through every tab in the workbook. Any help with the VBA?

 

[Trebor76]

Hi skghkjlssgh,

Welcome to MrExcel!!

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim varMyValue As Variant
    Dim rngMyValue As Range, rngFind As Range, rngRow As Range, rngDelete As Range
    Dim strMyValLocation As String
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    Set rngMyValue = ThisWorkbook.Sheets("Sheet1").Range("A1")  '<-Location of where the value to be checked resides. Change to suit.
    strMyValLocation = "'" & rngMyValue.Worksheet.Name & "'!" & rngMyValue.Address
    
    For Each ws In ThisWorkbook.Sheets
        For Each rngRow In ws.UsedRange.Rows
            If InStr("'" & rngRow.Worksheet.Name & "'!" & rngRow.Address, strMyValLocation) = 0 Then 'Don't want to delete actual value being searched.
                On Error Resume Next
                    Set rngFind = ws.Rows(rngRow.Row).Find(What:=rngMyValue.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                On Error GoTo 0
                If Not rngFind Is Nothing Then
                    If rngDelete Is Nothing Then
                        Set rngDelete = rngFind
                    Else
                        Set rngDelete = Union(rngDelete, rngFind)
                    End If
                End If
            End If
        Next rngRow
        If Not rngDelete Is Nothing Then
            rngDelete.EntireRow.Delete
        End If
        Set rngDelete = Nothing
    Next ws
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert