Ho to create a macro for this custom filter?

Amstrad · Mar 21, 2011

Hi

I am trying to create a filter that will filter any text that ends:

 .js
.css
.gif
.jpg
.swf
.ico
.png

I would also like it to filter any text which contains:

 mailto:
javascript
favicon

I would like to create a macro do do this in one go. How do I do this?

Many thanks for your help/

Weaver · Mar 21, 2011

I'd suggest you take a look at the advanced filter, as this allows a greater variety of filtering criteria.

Amstrad · Mar 21, 2011

Thanks for that. I recorded a macro that contains 2 of the filter terms that I want:

Code:

Sub CustomFilter()
'
' CustomFilter Macro
'

'
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$O$250").AutoFilter Field:=6, Criteria1:="=*.gif", _
        Operator:=xlOr, Criteria2:="=*favicon*"
End Sub

I have tried to manually edit this and insert the other filter variables that I want to use, but it doesn's seem to work:

Sub CustomFilter()
'
' CustomFilter Macro
'

'
Selection.AutoFilter
ActiveSheet.Range("$A$1:$O$250").AutoFilter Field:=6, Criteria1:="=*.js", _
Operator:=xlOr,
Criteria2:="=*.js", _
Operator:=xlOr,

Criteria3:="=*.css", _
Operator:=xlOr,

Criteria4:="=*.gif", _
Operator:=xlOr,

Criteria5:="=*.jpg", _
Operator:=xlOr,

Criteria6:="=*.swf", _
Operator:=xlOr,

Criteria7:="=*.ico", _
Operator:=xlOr,

Criteria7:="=*.png", _
Operator:=xlOr,

Criteria8:="=*favicon*", _
Operator:=xlOr,

Criteria9:="=*mailto*", _
Operator:=xlOr,

Criteria10:="=*java*"

End Sub

Also, how can I adapt the macro so that it filters all columns, not just a single one? I would also like the sheet range to be for the whole spreadsheet, not just $A$1:$O$250.

Many thanks for your help

Weaver · Mar 21, 2011

Sorry, I should've said 'advanced filter', not auto filter.

With advanced filter, you create a custom set of criteria on another sheet and use as a reference.

What you'll need to do is

1. Create another sheet. Call it Sheet2. Cell A1 should be exactly the same as the text in the header of column 6 (F I'm assuming) of your data set.
2. Populate with your criteria in a column. Use * for wildcards. So the file endings should all be *.jpg etc. anything that could appear at any point should be *java*. Your data should look like below

Excel Workbook

A

1

header6

2

*.js

3

*.css

4

*.gif

5

*.jpg

6

*.swf

7

*.ico

8

*.png

9

*favicon*

10

*java*

11

*mailto*

Sheet2

3. Assuming your data is on sheet1 and starts in cell A1, run the following code

Code:

Sub advFilter()
    With Sheets("sheet1")
        .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Sheet2").Range("A1:A11"), Unique:=False
    End With
End Sub

HTH

Amstrad · Mar 21, 2011

That's a real help. Thank you very much!

Just one question, There are 5 columns of text that I want to run this filter for. They are called Broken Link 1, Broken Link 2, Broken Link 3, Broken Link 4 and Broken Link 5 which are in columns F,G,J,L and N. Instead of having to go in and manually change cell A1 in Sheet2 to filter each individual column, how can I get it to filter all 5 at once?

thanks again.

Weaver · Mar 21, 2011

try setting up like this

Excel Workbook

A

B

C

D

E

1

Broken Link 1

Broken Link 2

Broken Link 3

Broken Link 4

Broken Link 5

2

*.js

3

*.css

4

*.gif

5

*.jpg

6

*.swf

7

*.ico

8

*.png

9

*favicon*

10

*java*

11

*mailto*

12

*.js

13

*.css

14

*.gif

15

*.jpg

16

*.swf

17

*.ico

18

*.png

19

*favicon*

20

*java*

21

*mailto*

22

*.js

23

*.css

24

*.gif

25

*.jpg

26

*.swf

27

*.ico

28

*.png

29

*favicon*

30

*java*

Sheet2

Excel Workbook
A B C D E
31 *mailto*
32 *.js
33 *.css
34 *.gif
35 *.jpg
36 *.swf
37 *.ico
38 *.png
39 *favicon*
40 *java*
41 *mailto*
42 *.js
43 *.css
44 *.gif
45 *.jpg
46 *.swf
47 *.ico
48 *.png
49 *favicon*
50 *java*
51 *mailto*
Sheet2
Excel 2007

Weaver · Mar 21, 2011

Not sure if this will work tho worth a try.

Of course you'll need to change "A1:A11" in the code to "A1:E51"

Ho to create a macro for this custom filter?

Amstrad

New Member

Excel Facts

Weaver

Well-known Member

Amstrad

New Member

Weaver

Well-known Member

Amstrad

New Member

Weaver

Well-known Member

Weaver

Well-known Member

Similar threads

Forum statistics

Share this page

Ho to create a macro for this custom filter?

Amstrad

New Member

Excel Facts

Weaver

Well-known Member

Amstrad

New Member

Weaver

Well-known Member

Amstrad

New Member

Weaver

Well-known Member

Weaver

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock