Autofilter issue when there is space in between two words

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165
Hello All,

I have come across this strange issue.

I am using vba to autofilter. So one column has all the criteria required to filter. The column I used is W. So W1 is 'John Musk' and W2 is just 'Peter'. I have W1 and W2 as a named range.

Now, I use vba to autofilter with the named range as criteria and have noticed that it just filters 'Peter' and not 'John Musk' even though it has 'John Musk' in the data.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Autofilter requires (assumes) that the data includes a header row.
 
Upvote 0
No, I mean the column labels that appear in the top row:

Hdr1
Hdr2
DataData
DataData
DataData
DataData
 
Upvote 0
Sure, I have it. My data has more than 20,000 rows with first row as label.

This is the issue:
1) If my criteria range is just one criteria, so w1 is 'Peter' then the autofilter works fine.
2) If my criteria range is just one criteria, so w1 is 'John Must' then the autofilter DOES NOT work.
3) If my criteria range has two criteria, so w1 is 'John Must' and w2 is 'Peter', then the autofilter ONLY filters 'Peter'.

My criteria is a dynamic name range, so it becomes just w1 if there is only one value and w1 & w2 if there are both etc.
 
Upvote 0
You're using Advanced Filter, not AutoFilter.
 
Upvote 0
Works fine for me. Before filter:

Row\Col
A​
B​
1​
Hdr2
2​
Peter Piper
3​
John Must
4​
5​
Hdr1
Hdr2
6​
DataPeter Piper
7​
DataJohn Must
8​
DataBill Bird
9​
DataBig Bob
10​
DataPeter Piper

After:

Row\Col
A​
B​
1​
Hdr2
2​
Peter Piper
3​
John Must
4​
5​
Hdr1
Hdr2
6​
DataPeter Piper
7​
DataJohn Must
10​
DataPeter Piper
 
Upvote 0
This is my code:

firstcriteria = Sheets("Settings").Range("RDS_Name").Value

RDS_Name is dynamic range I mentioned about.

Range("A1").AutoFilter Field:=7, Criteria1:=Split(Join(Application.Transpose(firstcriteria))), Operator:=xlFilterValues

I am trying to autofilter a criteria which is dynamic. I am not sure if I have explained it correctly.
 
Upvote 0
What is the default delimiter for Split and Join if none is specified?
 
Upvote 0
OK got it. Join uses SPACE as default delimiter and Split uses SPACE as default delimiter, this is causing the text with space in between to be treated as two different criteria. The following code worked:

Range("A1").AutoFilter Field:=10, Criteria1:=Split(Join(Application.Transpose(firstcriteria), ","), ","), Operator:=xlFilterValues

Thanks for the inputs!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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