Advanced Filter with wildcards, Excel 2013

[redrockmtn]

[FONT=&quot]I have an Excel list with many websites. Some end in .com, .net, .org. But some end in .com.mx, .fr, .ru. How do I do an advanced filter to remove all .com.mx, .fr, and .ru while keeping all .com, .net, and .org?[/FONT]

 

[D3allamerican07]

redrockmtn,

If the list exists in Column A, then try:

Sub Test()

    ActiveSheet.AutoFilterMode = False
 
        ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:=Array( _
            ".fr", ".com.mx", ".ru"), Operator:=xlFilterValues
        
            If ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 1).Value <> "" Then
                ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
    
    ActiveSheet.AutoFilterMode = False
    
End Sub

 

[Fluff]

@redrockmtn
There's some good info here relating to advanced filters, including wildcards
https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/#wildcard-characters

 

[redrockmtn]

Where do I enter this code? I was thinking of using the "advanced filter" in Excel.

redrockmtn,

If the list exists in Column A, then try:

Sub Test()

    ActiveSheet.AutoFilterMode = False
 
        ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:=Array( _
            ".fr", ".com.mx", ".ru"), Operator:=xlFilterValues
        
            If ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 1).Value <> "" Then
                ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
    
    ActiveSheet.AutoFilterMode = False
    
End Sub

 

[redrockmtn]

I am looking for a way to remove non .com ending websites in a list. When I add the filter *.com it still leaves websites that end with .com.mx, .com.tr etc.

@redrockmtn
There's some good info here relating to advanced filters, including wildcards
https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/#wildcard-characters

 

[Fluff]

it shouldn't
*.com should show anything ending in .com
*.com* should show anything containing .com

 

[redrockmtn]

It doesn't. I just tested it again.

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]websites[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]0123movies.com[/TD]
[/TR]
[TR]
[TD="colspan: 2"]01net.com[/TD]
[/TR]
[TR]
[TD="colspan: 2"]104.com.tw[/TD]
[/TR]
[TR]
[TD="colspan: 2"]1111.com.tw[/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]websites[/TD]
[/TR]
[TR]
[TD]*.com[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

it shouldn't
*.com should show anything ending in .com
*.com* should show anything containing .com

 

[redrockmtn]

It does, I just tested it again.

websites
0123movies.com
01net.com
104.com.tw


websites
*.com

 

[redrockmtn]

This is under the "data" tab, in "sort and filter" section, and "Advanced filter." Excel 2013. It requests for list range and criteria range.

 

[Marcelo Branco]

Try
="=*.com"

not
*.com


M.