Use VBA to delete lines with a cell-determined criterion

Question

I need a Vba that does the following action: The moment it is activated, read a specific cell and delete lines from another tab taking into account the specific cell as a criterion. Example:
In Planilha1 I have two products:
Camisa Azul A
Camisa Azul B
But in worksheet 2 in cell D2 is written only "Blue". I need this code to consider the text present in cell D2 of Worksheet2 and delete the lines where both shirts are present in Worksheet 1, considering only the word "Blue" present in the texts within the cells.

I use the code below normally

lLast = Planilha1.UsedRange.Rows.Count

For lRow = lLast To 2 Step -1
If _
Cells(lRow, "A") Like "*Critério*" Then
Rows(lRow).Delete
    End If
Next lRow`

But in that case I couldn’t reference the cell, if I could use it like I would?

Answer 1

Example

Assuming two spreadsheets, one called "Sheet 1" and another "Sheet 2".

The data are inserted in "Planilha1", according to the following table:

|   |       A        |
|---|----------------|
| 1 |                |
| 2 | Camisa Azul A  |
| 3 | Camisa Verde A |
| 4 | Camisa Azul B  |
| 5 | Camisa Verde B |

And in cell "D2" of "Planilla2" the word Blue

Code

Dim UltimaLinha As Long, i As Long
Dim ref As String
Dim Rng As Range, nova_rng As Range
Dim ws_ref As Worksheet, ws_dest As Worksheet
Set ws_ref = ThisWorkbook.Worksheets("Planilha2")
Set ws_dest = ThisWorkbook.Worksheets("Planilha1")
'Referência
ref = ws_ref.Range("D2")

With ws_dest
    UltimaLinha = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = UltimaLinha To 2 Step -1
        If Not IsError(Application.Match("*" & ref & "*", .Range("A" & i), 0)) Then
            If Rng Is Nothing Then Set Rng = .Range("A" & i) 'Define o primeiro item da range para não ocorrer erro na função Union
            Set nova_rng = .Range("A" & i)
            Set Rng = Union(Rng, nova_rng)   'Cria a range não contígua para deletar
        End If
    Next i
End With
Rng.EntireRow.Delete

Explanation

• Assign to reference sheet ws_ref, where the cells met with the reference criterion are located.
• Assign to destination worksheet ws_dest with the value of the Reference Sheet.
• Assigns the value of D2 to the variable ref
• Find the Last Row of Column A of the spreadsheet ws_dest
• Loop the Last Line to the second, looking for a Match of the value of ref
• It creates a Union that is not contiguous with the cells of the values found. Union is created because it is faster to delete everything at once than to delete line by line.
• Deletes the entire line from the desired range with Rng.EntireRow.Delete or if you just want to clean the entire line Rng.EntireRow.Clear

Obs.: For more information on other ways to accomplish this see this answer

Upshot

|   |       A        |
|---|----------------|
| 1 |                |
| 2 | Camisa Verde A |
| 3 | Camisa Verde B |

Note:

Deleting/Deleting data is very sensitive and complicated as they can be lost forever. I suggest backing up the old data and creating a copy of the spreadsheet/data for testing.

Answer 2

For Each cell In [Tabela1[Coluna1]]
    If cell = "critério" Then Rows(cell.Row).Delete
Next

For Each cell In Range("A2:A1000")
    If cell = "critério" Then Rows(cell.Row).Delete
Next