filtering in VBA is not working for me..

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
HI,

I'm trying to filter with a macro but get stuck in the criterial part.
in sheet3 cell x1 I have "950 Seafood". This is what I want to filter by in sheet 4

Range("A6").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Sheets("3").[x1].Value

The above formula doesn't seem to work, any ideas??
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
how about;
Code:
Range("A6:A"&rows.count).Select 
Selection.AutoFilter 
Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet3").[x1].Value
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi

You can also try:

Code:
Range("A6").AutoFilter Field:=1, Criteria1:=Sheets("Sheet3").Range("X1").Value

Hope this helps
PGC
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
PGC -
for some reason, your code will not work for these data layout.
Book1
ABCD
1
2
3
4
5
6
7950 Seafood
8
9
10
11
12950 Seafood
13
14
15
16950 Seafood
17
18
19
20950 Seafood
Sheet4
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi agihcam

I defined the range for the autofilter using a cell. Autofilter will automatically extend the range down in the current area.

Since you have empty cells in the range you want to autofilter, I would use:

Code:
Range("A6", Range("A" & Rows.Count).End(xlUp)).AutoFilter _
          Field:=1, Criteria1:=Sheets("Sheet3").Range("X1").Value

Thanks for pointing this case out.

Kind regards
PGC
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
filter by blanks in macro won't work

Thanks, that helped al lot.

But, I'm using the formula below in a macro and its suppose to filter by non blanks. It won't work, any ideas


Selection.AutoFilter Field:=1, Criteria1:="<>"
 

Watch MrExcel Video

Forum statistics

Threads
1,113,812
Messages
5,544,458
Members
410,613
Latest member
Texman
Top