CODEWITHSUNDEEP
vbaexcel
slekk
slekk

Reputation: 29

How to delete an entire row if two cells contain specific text

I need VBA code which can delete an entire row, if two cells (which can be in any column of the sheet) contains a specific text.

I have a daily generated print reporting file and in this file exists the text (in a non specific column) "printed BlackWhitepages, total = #" and "printed Colourpages, total = #" also in any column.

The # stands for a number, so in my file there could be 0-99999...., depending on how many pages were printed.

The file has columns from A to BA and thousands of rows, and the Blackwhitepages and Colourpages text could be on each column, depending on the print job.

I want the VBA to look at the entire sheet, and if a row contains "printed BlackWhitepages, total = 0" and "printed Colourpages, total = 0", the entire row should be deleted.

Upvotes: 0

Views: 1248

Answers (1)

izzymo
izzymo

Reputation: 936

Try the following Code:

Sub DeleteRow()

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    VarBnW = 0
    VarCol = 0
    For Each cell In Range("A" & i & ":" & "BA" & i)

        If Trim(cell.Value) = "Gedruckte Schwarzweißseiten, insgesamt = 0" Then VarBnW = 1
        If Trim(cell.Value) = "Gedruckte Farbseiten, insgesamt = 0" Then VarCol = 1

        If VarCol + VarBnW = 2 Then
            MsgBox cell.Address
            cell.EntireRow.Select
            Selection.Delete
            i = i - 1
        End If

    Next cell
Next i

End Sub

Upvotes: 1

Related Questions