Delete Filtered Rows

Jones1413

New Member
Joined
Jul 26, 2019
Messages
25
Hello,

I have a macro that auto filters my criteria in Column I. Once that is filtered, I want to delete all of the hidden rows that are not included in on the filter. I do not want to create a loop because it is a large data set and will take too long to loop through each line. Is there anyway to make this faster to auto delete all rows that are not showing after I apply the filter?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,424
Office Version
  1. 365
Platform
  1. Windows
Are you filter criteria always the same & if so what are they?
 

Jones1413

New Member
Joined
Jul 26, 2019
Messages
25
Yes, my criteria is always the same. I use the array below for my auto filter:

Once it is filtered, I wanted to delete every row that doesn't contain the filtered content. Again, it is a large data set and don't want to use a loop to find the bottom of the data because it would take too long and the number of lines of data changes every day/week on the report.

VBA Code:
With Selection
    .AutoFilter
    .AutoFilter Field:=9, Criteria1:=Array("Apples", "Oranges", "Bananas", "Grapes", _
    "Pineapple"), Operator:=xlFilterValues
    
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,424
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Jones()
   Dim Cl As Range
   Dim Txt As String
   
   Txt = "|Apples|Oranges|Bananas|Grapes|Pineapple|"
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("I2", Range("I" & Rows.Count).End(xlUp))
         If InStr(1, Txt, "|" & Cl.Value & "|", 1) = 0 Then .Item(Cl.Value) = Empty
      Next Cl
      x = .Keys
      Range("A1:I1").AutoFilter 9, .Keys, xlFilterValues
      ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
      ActiveSheet.AutoFilterMode = False
   End With
End Sub
 

Jones1413

New Member
Joined
Jul 26, 2019
Messages
25
Awesome. Thank you Fluff, that worked great!

Out of curiosity, what would the macro look like if my criteria changed? I do have a report that I filter based on different criteria. I have a macro that filters most of the data I need but sometimes I have to go in and manually check additional criteria to show visible on the worksheet. I would then like to have a macro auto delete the rows that are hidden.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,424
Office Version
  1. 365
Platform
  1. Windows
As long as the criteria are always an exact match just change this line to include the other values you want to keep
VBA Code:
Txt = "|Apples|Oranges|Bananas|Grapes|Pineapple|"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,373
Messages
5,624,309
Members
416,020
Latest member
ANDREAC247

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