CODEWITHSUNDEEP
vbaexcel
Defca Trick
Defca Trick

Reputation: 315

Excel Vba Macro to Delete Entire Row Based on Column Header

Would the below code be able to be modified to

  1. remove multiple rows based on column headers and cell values, and
  2. 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

Upvotes: 0

Views: 1272

Answers (2)

DougLSchneider
DougLSchneider

Reputation: 26

Autofilter is more efficient than looping

Sub DeleteRows()
    Sheet1.Range("a1:c35").AutoFilter Field:=2, Criteria1:="Completed"
    Sheet1.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Sheet1.UsedRange.AutoFilter
'repeat for each value
End Sub

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 1

Related Questions