Excel Vba Macro to Delete Entire Row Based on Column Header

Question

Would the below code be able to be modified to

remove multiple rows based on column headers and cell values, and
do this for multiple column row combinations?

example: Column "Status" Value "Complete" Cycle through all sheets and look for any header that says status and delete all rows where status has a complete in it?

Sub Delete_Rows_Based_On_Header_and_Value ()
'
' Delete_Rows_Based_On_Header_and_Value Macro
'
' Declaration
Dim a as long
Dim w as long
Dim vDELCOLs as variant
Dim vCOLNDX as variant
Dim vDELROWs as variant
Dim vROWNDX as variant

vDELCOLs = array("status","Status Name","Status Processes")
vDELROWs = array("Complete","Completed","Done")

with Activeworkbook
    for w=1 to .worksheets.count
        with worksheets(w)
' I know this part is to delete columns based on the column name and I am not sure how to modify it to just check column name then delete row based on the value on that column only.
            for a=lbound(vdelcols) to ubound(vdelcols)
                vcolndx=application.match(vdelcols(a), .rows(1), 0)
                if not iserror(vcolndx) then
                    .columns(vcolndx).entirecolumn.delete
                end if
            next a
        end with
    next w
end with

Before you can write any more code, you will need to decide if you want to delete a row if (a) the status column contains "Complete" OR the status name column contains "Completed" OR the status processes column contains "Done", or (b) the status column contains "Complete" AND the status name column contains "Completed" AND the status processes column contains "Done". — YowE3K
So it would be easier to do this for 1 column name and 1 cell value ie: Status and Complete then another line for status name and completed and a third line for status process and done right? so that would simplify the code and the process? — Defca Trick
It would be marginally simpler to do it for one value - it would save thinking about whether you want all the values to match or any value to match. But if you are willing to just check one column for its value then you probably have already decided that you want the row deleted if any value matches. — YowE3K
And it can be 1 column for multiple values? so Status I want to remove Complete and Pending -- Then once all of those are deleted I can select Status Name and Completed and Pending etc. so in that case I just need it to look for a certain column, then look for an array of values and delete the entire row if any of those values exist right? - also ty for editing my post, sorry if it was not clear enough! — Defca Trick

YowE3K YowE3K · Accepted Answer · 2017-03-16T00:29:40

The following code will take an array of arrays as vDELROWS and will delete a row if any of the values match what is in the corresponding column.

Sub Delete_Rows_Based_On_Header_and_Value()
    '
    ' Delete_Rows_Based_On_Header_and_Value Macro
    '
    ' Declaration
    Dim a As Long
    Dim w As Long
    Dim vDELCOLs As Variant
    Dim vCOLNDX As Variant
    Dim vDELROWs As Variant
    Dim vROWNDX As Variant
    Dim r As Long
    Dim v As Long

    vDELCOLs = Array("status", "Status Name", "Status Processes")
    vDELROWs = Array(Array("Complete", "Pending"), Array("Completed", "Pending"), Array("Done"))

    With ActiveWorkbook
        For w = 1 To .Worksheets.Count
            With Worksheets(w)
                For a = LBound(vDELCOLs) To UBound(vDELCOLs)
                    vCOLNDX = Application.Match(vDELCOLs(a), .Rows(1), 0)
                    If Not IsError(vCOLNDX) Then
                        For r = .Cells(.Rows.Count, vCOLNDX).End(xlUp).Row To 1 Step -1
                            For v = LBound(vDELROWs(a)) To UBound(vDELROWs(a))
                                If .Cells(r, vCOLNDX).Value = vDELROWs(a)(v) Then
                                    .Rows(r).EntireRow.Delete
                                    Exit For
                                End If
                            Next
                        Next
                    End If
                Next a
            End With
        Next w
    End With
End Sub

Excel Vba Macro to Delete Entire Row Based on Column Header

2 Answers