delete rows based on criteria in multiple sheets

Question

I used this code to look for the words written in in Cell (10,2) in different rows of table in multiple Sheets in the same workbook, and when found the word, the code will delete the entire row in each table, the issue is that the code is applied in the first sheet where the command button is on and not applied on other sheets, so please your help in this.

sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String

For Each WS In ThisWorkbook.Worksheets
    With WS
        pattern = Cells(10, 2) ' delete row if found the word total in it
        RowCount = ActiveSheet.UsedRange.Rows.Count

        Dim i As Integer
        For i = 2 To RowCount
            Dim j As Integer
            For j = 1 To 3 'find the word within this range
                If Cells(i, j) = pattern Then
                    Cells(i, j).EntireRow.Delete 
                End If
            Next j
        Next i
    End With
Next WS

End Sub

Many Thanks Shai for your kind assistance, it helps a lot

Nabil Amer
– Nabil Amer

2017-02-13 06:56:53 +00:00
Commented Feb 13, 2017 at 6:56 — Nabil Amer
– Nabil Amer, Commented Feb 13, 2017 at 6:56

Shai Rado · Accepted Answer · 2017-02-12 13:00:30Z

2

You need to fully qualify all your Range and Cells inside the With WS statement, by adding the . as a prefix.

E.g. instead of pattern = Cells(10, 2) use pattern = .Cells(10, 2) , the .Cells(10, 2) means Cells(10, 2) of WS , which is being advanced in your For Each WS In ThisWorkbook.Worksheets.

Code

Option Explicit

Sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String
Dim RowCount As Long, i As Long, j As Long

For Each WS In ThisWorkbook.Worksheets
    With WS
        pattern = .Cells(10, 2) ' delete row if found the word total in it
        RowCount = .UsedRange.Rows.Count

        For i = 2 To RowCount
            For j = 1 To 3 'find the word within this range
                If .Cells(i, j) = pattern Then
                    .Cells(i, j).EntireRow.Delete
                End If
            Next j
        Next i
    End With
Next WS

End Sub

Option 2: Instead of using two For loops, you could replace the 2nd For loop with the Application.Match function, to look for a certain value throughout the row.

Code with Match

Option Explicit

Sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String
Dim RowCount As Long, i As Long, j As Long

For Each WS In ThisWorkbook.Worksheets
    With WS
        pattern = .Cells(10, 2) ' delete row if found the word total in it
        RowCount = .UsedRange.Rows.Count

        For i = 2 To RowCount
            ' use the Match function to find the word inside a certain row
            If Not IsError(Application.Match(pattern, .Range(.Cells(i, 1), .Cells(i, 3)), 0)) Then '<-- match was successful
                .Cells(i, 1).EntireRow.Delete
            End If                            
        Next i
    End With
Next WS

End Sub

Edit 2:

Option Explicit

Sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String
Dim FirstRow As Long, RowCount As Long, i As Long, j As Long
Dim FirstCol, ColCount As Long

For Each WS In ThisWorkbook.Worksheets
    With WS
        pattern = .Cells(10, 2) ' delete row if found the word total in it
        FirstRow = .UsedRange.Row
        RowCount = .UsedRange.Rows.Count
        FirstCol = .UsedRange.Column
        ColCount = .UsedRange.Columns.Count

        For i = 2 To RowCount + FirstRow
            ' use the Match function to find the word inside a certain row
            If Not IsError(Application.Match(pattern, .Range(.Cells(i, 1), .Cells(i, ColCount + FirstCol)), 0)) Then '<-- match was successful
                .Cells(i, 1).EntireRow.Delete
            End If
        Next i
    End With
Next WS

End Sub

edited Feb 12, 2017 at 13:00

answered Feb 12, 2017 at 11:02

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nabil Amer Over a year ago

firstly, thanks for your kind assistance. I tried the second code it works well but it deleted the matched words in Cells(10,2) in all sheets only and ignored any other similar words in other columns or rows. to express myself better. say I want to delete any row in any sheets that has the word Red Apple. so would you please assist?

Shai Rado Over a year ago

@NabilAmer you want to remove the word found in Cells(10,2) from the entire sheet ? cause your post you are looking at columns A to C only

Nabil Amer Over a year ago

Hi Shai, I tried the Edit 2 it works only if the Cells (10,2) have the required word but if not found it will not work on other sheets. the cells(10,2) is in sheet1 which needs to be exclude, so I added the following code and it works as needed (pattern = Sheets("Sheet1").Cells(10, 2) and also this (If WS.Name <> "Sheet1" Then). now it works well. I have one more question if it could be, how can I use Input box without asking me to repeat the entry of the required data as I used this code also pattern = InputBox("Please enter the selected ID.")

Shai Rado Over a year ago

@NabilAmer not sure I understand, you want to enter the value in the InputBox only once ? if that's the case just put this code line before the For Each WS In ThisWorkbook.Worksheets line

Nabil Amer Over a year ago

Great Many thanks Boss, it works very well. appreciate it

|

Nabil Amer · Accepted Answer · 2017-02-13 06:56:05Z

Sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String
Dim FirstRow As Long, RowCount As Long, i As Long, j As Long
Dim FirstCol, ColCount As Long

For Each WS In ThisWorkbook.Worksheets
    With WS
        pattern = Sheets("Sheet1").Cells(10, 2) ' delete row if found the word in this source sheet
        FirstRow = .UsedRange.Row
        RowCount = .UsedRange.Rows.Count
        FirstCol = .UsedRange.Column
        ColCount = .UsedRange.Columns.Count

        For i = 2 To RowCount + FirstRow
            ' use the Match function to find the word inside a certain row
            If WS.Name <> "Sheet1" Then 'I added this to exclude the said sheet as a source page
            If Not IsError(Application.Match(pattern, .Range(.Cells(i, 1), .Cells(i, ColCount + FirstCol)), 0)) Then '<-- match was successful
            .Cells(i, 1).EntireRow.Delete
            End If
            End If
        Next i
    End With
Next WS

End Sub

Collectives™ on Stack Overflow

delete rows based on criteria in multiple sheets

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related