CODEWITHSUNDEEP
excelvbams-office
marouane91
marouane91

Reputation: 39

Delete multiple rows across multiple sheets containing the same keyword

I have an excel file that contains around 50 sheets, all sheets have the same columns.

However, i would like to delete the entire rows that contains the same keyword "MARTIN" located in the 6th/last column in all the sheets.

I have tried to select all sheets at once and then delete the rows but it only worked for one sheet.

So if there is a way to do that either through an excel option or vba i'm all ears to suggestions.

Thank you

Upvotes: 0

Views: 2228

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You could try:

Option Explicit

Sub test()

    Dim ws As Worksheet
    Dim LastRow As Long, i As Long

    'Loop all sheets
    For Each ws In ThisWorkbook.Worksheets

        With ws
            'Find the last row of column 6
            LastRow = .Cells(.Rows.Count, 6).End(xlUp).Row

            'Loop all rows in column 6 starting from the last one
            For i = LastRow To 1 Step -1
                'If the value is "MARTIN" then delete
                If .Cells(i, 6).Value = "MARTIN" Then
                    .Rows(i).EntireRow.Delete
                End If

            Next i

        End With

    Next ws

End Sub

Upvotes: 0

A.Milosevic
A.Milosevic

Reputation: 55

A quicker way to do it is to use shortcuts to delete the row and then go to the next sheet:

Say you want to delete the 6th row:

  1. Go to the 6th row and hit Shift+Space this will highlight the entire row
  2. To delete the row Ctrl + - (Ctrl and the minus sign)
  3. Then to go from sheet one to sheet two you Ctrl + Page Down btn

If you really want to save time and know VBA you can create a short script. Unfortunately, I'm on my mac so I can't test one out right now but I found a good link that gives you a step by step here

Hope this helps!

Upvotes: 0

Related Questions