Delete Row based on specified value in specified Column

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
49
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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
---------------
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
    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.
Code:
[COLOR=#574123]With .Cells(Lrow, c)[/COLOR]
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
49
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 26, 2019
Messages
49
Office Version
  1. 365
  2. 2011
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Try this:
Code:
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
 
Last edited:

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
49
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
If it is only two, I would just simply repeat this section under the current one, changing the pertinent value:
Code:
'   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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,742
Members
417,165
Latest member
Hilders1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top