MS Excel - Macro to Delete particular values in Separate Tabs

santa12345

Board Regular
Joined
Dec 2, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows
Hello.
I am trying to write a macro to do the following.
I have a list of values that I would like to find on each tab and remove the entire row of data.
Please reference the attachment. Hopefully that will help.
Unfortunately my real example ...the tabs will be randomly named with over 100+ tabs.
Any guidance would be greatly appreciated.
Thanks !!!!
 

Attachments

  • test.png
    test.png
    56.2 KB · Views: 12

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
try this on a copy of you file.

VBA Code:
Sub delete_car()
Application.ScreenUpdating = False
For Each ws In Worksheets

If ws.Name = ActiveSheet.Name Then GoTo 99

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row

car = Cells(r, "A")

On Error Resume Next
    With ws.Range("A:A")
        .Replace car, False, xlWhole ' can also use *car* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
On Error GoTo 0

Net r

99 Next ws
Application.ScreenUpdating = True
End Sub

hth,
Ross
 
Upvote 0
Hello Santa,
here is one more...
Change sheet name in the code or rename your sheet.
VBA Code:
Sub RemoveErrorRows()
  
    Dim varWS As Worksheet, varWS2 As Worksheet
    Dim varNRows As Long, varNRows2 As Long
    Dim varRange1 As Range, varRange2 As Range, _
        varRange3 As Range, varRange4 As Range
    Dim varNErrors As Long
  
    Application.ScreenUpdating = False
    Set varWS = Worksheets("YourSheetName")
    varNRows = varWS.UsedRange.Rows.Count
    Set varRange2 = varWS.Range("A2:A" & varNRows)
    For Each varWS2 In Worksheets
        If Not varWS2.Name = "YourSheetName" Then
            For Each varRange1 In varRange2
                varNRows2 = varWS2.UsedRange.Rows.Count
                Set varRange4 = Sheets(varWS2.Index).Range("A1:A" & varNRows2)
                varRange4.AutoFilter 1, varRange1.Value
                varNErrors = WorksheetFunction.CountIf _
                    (Sheets(varWS2.Index).Range("A2:A" & varNRows2), varRange1.Value)
                If varNErrors > 0 Then
                    Application.DisplayAlerts = False
                    Sheets(varWS2.Index).Range("A2:A" & varNRows2). _
                    SpecialCells(xlCellTypeVisible).Delete
                End If
            Next
            varWS2.ShowAllData
            varRange4.AutoFilter
        End If
    Next
    Application.ScreenUpdating = True
  
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top