Thread: Deleting rows with certain data in them

Hi all, a long shot maybe but I am after the following.

I have a sheet of data approx 3000 rows (20 columns) which is given to me every week. This data is in the same format with all information being in the same column each wee, however the data in each column changes values / text etc. From this data a pivot chart is created to calculate our largest downtime pieces of equipment.

Some of the rows we want to delete every week as the breakdown is nothing to do with our dept. These rows can be anywhere in the sheet.

The rows we want to delete will always have the data to be identified as unwanted in the same column i.e the fault description always appears in column 'M'.

What I was thinking of was a way of having a list of faults (that we do not want included in our data) on one sheet and then a macro that runs through the original data and deletes all the rows with the corresponding fault in the unwanted list sheet.

The list of faults to be deleted will grow over time and so must be variable.

Is this possible?

Thanks for any help or guidance given.

greg7468,

Is this possible?

Yes, please post your workbook.

Click on "New Post" - scroll down and see "Manage Attachments".

Here is a start you can try adapting.

Assuming a list of faults is kept on, say, sheet2

On Sheet1 . . .

Place a match formula in a helper column (or a countif) to match items in the source against the list of faults not wanted.

Filter the above column for values >=1 (match found)

Delete all visible rows

Adjust the code per your requirements.

Code:

Option Explicit

Sub Delete_Rows_By_Criteria()
'http://www.excelforum.com/excel-general/722203-deleting-rows-with-certain-data-in-them.html

    Dim lrow As Long
    
    lrow = Sheet1.Cells(Rows.Count, "M").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook
        .Names.Add Name:="Faults", RefersToR1C1:="=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1),1)"
    End With
    
    With Sheet1
        If .AutoFilterMode = True Then .AutoFilterMode = False
        .Range("Z1").Value = "Match Default"
        .Range("Z2").FormulaR1C1 = "=MATCH(RC[-13],Faults,0)"
        .Range("Z2").AutoFill Destination:=.Range("Z2:Z" & lrow)
        .Range("Z1:Z" & lrow).AutoFilter Field:=1, Criteria1:=">=1", Operator:=xlAnd
        .Range("Z1").CurrentRegion.Offset(1, 0).Resize(, 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
        .Range("Z1").CurrentRegion.Resize(, 1).ClearContents
    End With

    Application.ScreenUpdating = True

End Sub