Delete Row based on specified value in specified Column

[Dustinkli]

Good Day,

I am having trouble with an excel formula. I would like for my VBA script to delete all rows where a specified word exists in a specified column. My problem, however, is that the columns can sometimes change so I would like the script to be based on the headers rather than the column letter.

Here's what I have so far:

---------------
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = LastRow To Firstrow Step -1

With .Cells(Lrow, "H")

If Not IsError(.Value) Then

If Not .Value = "SampleValue1" Then .EntireRow.Delete

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
---------------

 

[Joe4]

You can use FIND to locate which column a certain header appears in, then capture that column number, and then incorporate that into your VBA code, i.e.

    Dim c As Long

'   Find the word "dog" in row 1 and return the column number
    c = Rows("1:1").Find(What:="dog", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
    MsgBox "Header appears in column number: " & c

You can then use that in your range references, using Cells, i.e.

[COLOR=#574123]With .Cells(Lrow, c)[/COLOR]

 

[Dustinkli]

I am not sure how to implement that. I keep getting a Type mismatch error.

Also I want to avoid message boxes a this is 1 segment of a much larger script.

 

[Joe4]

Also I want to avoid message boxes a this is 1 segment of a much larger script.

LOL! That message box was just for demonstration purposes to show you what it was returning. You do not need it in there at all, and can remove it.

I can help you out if you provide the key details. What is the exact header you are looking for?

 

[Dustinkli]

In the header titled "Owned By Team" I'd like to delete every row where the value is not "Test123".

 

[Joe4]

Try this:

Sub MyMacro()

Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim c As Long

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

With ActiveSheet
    .Select
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    .DisplayPageBreaks = False

    Firstrow = 2
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
'   Find the specified value in row 1 and return the column number
    c = Rows("1:1").Find(What:="Owned By Team", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column

    For Lrow = LastRow To Firstrow Step -1
        With .Cells(Lrow, c)
            If Not IsError(.Value) Then
                If Not .Value = "Test123" Then .EntireRow.Delete
            End If
        End With
    Next Lrow

End With

ActiveWindow.View = ViewMode

With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With

End Sub

 

[Dustinkli]

Thank you. That works!

How would I go about adding in a second column with a second condition?

For instance if I want header named "Call Source" to remove row that isn't "Phone"?

 

[Joe4]

If it is only two, I would just simply repeat this section under the current one, changing the pertinent value:

'   Find the specified value in row 1 and return the column number
    c = Rows("1:1").Find(What:="[COLOR=#ff0000]Owned By Team[/COLOR]", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column

    For Lrow = LastRow To Firstrow Step -1
        With .Cells(Lrow, c)
            If Not IsError(.Value) Then
                If Not .Value = "[COLOR=#ff0000]Test123[/COLOR]" Then .EntireRow.Delete
            End If
        End With
    Next Lrow

If you are going to check more than 2, then it might be time to investigate checking out multi-dimensional arrays and loop through them.