Find Column then remove Rows that contain certain values

MusCorleone

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi,

I am trying to create a filtering macro where:

-In one sheet, I can put columns and values to look for in those columns, and have the macro delete the rows containing the said values.
1-Filters.JPG


-In another sheet is where the raw data will be pasted manually.
2-Report.JPG


So in the above example, once run, the macro should find column 'Notes' and then delete rows with the value 'internal', and then proceed to column 'Amount', and so on.

Any help is appreciated!

TIA
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi and welcome to MrExcel

Considering that your headings on both sheets are in row 3. Try the following macro, just adjust the name of both sheets.
VBA Code:
Sub DeleteRow()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lr As Long, lc As Long
  Dim f As Range, sCrit As String
  
  Application.ScreenUpdating = False
  Set sh1 = Sheets("data")      'raw data sheet
  Set sh2 = Sheets("criteria")  'criteria sheet
  
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  lr = sh1.Range("A" & Rows.Count).End(3).Row
  lc = sh1.Cells(3, Columns.Count).End(1).Column
  
  For i = 4 To sh2.Range("A" & Rows.Count).End(3).Row
    Set f = sh1.Rows(3).Find(sh2.Range("A" & i).Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      sCrit = sh2.Range("B" & i).Value
      If IsNumeric(sCrit) Then sCrit = "=" & sCrit Else sCrit = "=*" & sCrit & "*"
      With sh1.Range("A3", sh1.Cells(lr, lc))
        .AutoFilter Field:=f.Column, Criteria1:=sCrit
        .Offset(1).EntireRow.Delete
      End With
    End If
    If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  Next
  
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  Application.ScreenUpdating = False
End Sub
 
Upvote 0
Hi and welcome to MrExcel

Considering that your headings on both sheets are in row 3. Try the following macro, just adjust the name of both sheets.
VBA Code:
Sub DeleteRow()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lr As Long, lc As Long
  Dim f As Range, sCrit As String
 
  Application.ScreenUpdating = False
  Set sh1 = Sheets("data")      'raw data sheet
  Set sh2 = Sheets("criteria")  'criteria sheet
 
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  lr = sh1.Range("A" & Rows.Count).End(3).Row
  lc = sh1.Cells(3, Columns.Count).End(1).Column
 
  For i = 4 To sh2.Range("A" & Rows.Count).End(3).Row
    Set f = sh1.Rows(3).Find(sh2.Range("A" & i).Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      sCrit = sh2.Range("B" & i).Value
      If IsNumeric(sCrit) Then sCrit = "=" & sCrit Else sCrit = "=*" & sCrit & "*"
      With sh1.Range("A3", sh1.Cells(lr, lc))
        .AutoFilter Field:=f.Column, Criteria1:=sCrit
        .Offset(1).EntireRow.Delete
      End With
    End If
    If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  Next
 
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  Application.ScreenUpdating = False
End Sub


This is perfect! Worked exactly how I envisioned it. I just tweaked it a little bit to fit my desired output. You're the man, Dante! Thank you! Cheers
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top