Deleting Rows with text

[XLerator]

I have various data in a column, and I want to include deleting them in a formula macro.

For example, I want the macro to run so that whenever it sees ANY ROW which contains the Text XX, YY, or ZZ etc it erases it.


So the macro would basically look for any of those three values and delete the entire row they are in. It would keep deleting ALL the rows until there are no more occurrences of the text.

[Ivan F Moala]

Try something like:

Option Explicit

Option Base 1



Sub Delete_Txt()

Dim x As Single

Dim Kount As Double

Dim sSearch()



'// Define your string to search for here

sSearch() = Array("XX", "YY", "ZZ")

Kount = 0

Application.ScreenUpdating = False



For x = 1 To UBound(sSearch())

    On Error Resume Next

    Do

        Cells.Find(What:=sSearch(x), After:=ActiveCell, LookIn:=xlFormulas, LookAt _

            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

            False).Activate

        Selection.EntireRow.Delete

        If Err Then Exit Do

        Kount = Kount + 1

    Loop

    On Error GoTo 0

Next

Application.ScreenUpdating = True



MsgBox "Completed deleting " & Kount & " rows"



End Sub

[Jay Petrulis]

Hi,

Brute force method here. Try,

---begin VBA---
Sub Delete_Rows()
Dim lastrow As Long, lastcol As Integer
Dim x As Long, y As Integer, Counter As Long

Application.ScreenUpdating = False
With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column

For x = lastrow To 1 Step -1
Counter = 0
Counter = Counter + _
WorksheetFunction.CountIf(Range(Cells(x, 1), Cells(x, lastcol)), "XX") + _
WorksheetFunction.CountIf(Range(Cells(x, 1), Cells(x, lastcol)), "YY") + _
WorksheetFunction.CountIf(Range(Cells(x, 1), Cells(x, lastcol)), "ZZ")
If Counter <> 0 Then Rows(x).delete
Next x

End With

End Sub
---end VBA---

HTH,
Jay