vba macro defining a filter based on a array of cells with numbers and text values not working - please, help me

daviddg

New Member
Joined
Jan 29, 2014
Messages
1
Hi all,

I'm stuck at the moment with the following problem. I have researching and googling for a solution without luck...

Working with vba excel 2010

I have a workbook with two main tabs: PurchaseReport and CeCos_Tab

The PurchaseReport contains a range of cells where the first column have different elements, including numbers (figures) and texts (strings).

The CeCos_Tab has the list of elemenets (criteria) to be used to filter the former column.

There is an object defined which is: CritList=OFFSET(Criterias,1,0,COUNTA(CeCos_Tab!$A:$A)-1,1); note Criterias is the header of that list: Criterias=CeCos_Tab!$A$1. Assume it's working properly as I have already checked it.

As said the criteria list contains numbers and text values, here is a sample. Criterias: test0, 1, test1, 2, 3, 4, 5, 6, 7 and test2.

My code is:

Sub FilterRangeCriteria()
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range

Set wsO = Worksheets("PurchaseReport")
Set wsL = Worksheets("CeCos_Tab")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("CritList")

vCrit = rngCrit.Value

If wsO.FilterMode Then
wsO.ShowAllData
End If

rngOrders.AutoFilter _
Field:=1, _
Criteria1:=Application.Transpose(vCrit), _
Operator:=xlFilterValues
End Sub

This way, the filter only presents text (string) values of the criteria list; in my example: test0, test1 and test2.

Meanwhile, if the Criteria1 is "Criteria1:=Split(Join(Application.Transpose(vCrit))), _" the filter only offers the numbers (figures) values of the criteria list; in my example: 1, 2, 3, 4, 5, 6 and 7

How can I get both the texts values and the numbers in my filter criteria: test0, 1, test1, 2, 3, 4, 5, 6, 7, test2

Many thanks in advance! David
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,054
Messages
5,835,153
Members
430,342
Latest member
Sailingexcel

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