AutoFiltering Data COlumns

**labkhand** · 10-15-2013, 05:39 PM

Hi All,

I am trying to auto filter data worksheet based on several key data values. I have the following line of code which works but as you see the Array elements are hardcoded:

Code:

Rng.AutoFilter Field:=8, Criteria1:=Array("Request", "Final", "Initial", "Withdrawal")

I tried to construct the elements of the array dynamically in the VBA code by providing users a user selection form which contains several check boxes which are used by the VBA code to determine which values should be in the array used for autofiltering (above code) of the data worksheet. So if the InitialChkBox is selected then I update a variant variable with value of "Initial". The code looks as follows:

Code:

Dim vSubTypes As Variant

If (Worksheets(const_ParametersSheetName).InitialChkBox.Value = True) Then
  vSubTypes = Chr(34) & "Initial" & Chr(34) & ", "
end if

If (Worksheets(const_ParametersSheetName).InitialCloseOutChkBox.Value = True) Then
   vSubTypes = vSubTypes & Chr(34) & "Initial Close-Out" & Chr(34) & ", "
end if

When construction of vSubTypes is done, I remove the last comma and now this variant can be used to be my array elements sending for autofiltering. The modified autofiltering code looks as follows:

Code:

Rng.AutoFilter Field:=8, Criteria1:=Array(vSubTypes))

This code doesn't work and when I look at the data worksheet the autofilter of field 8 is all blank. I would appreciate your help fixing this issue. I need to be able to construct the Array elements by checking on the value of the related check boxes so that it can be passed to my function to autofilter the data worksheet.

Sorry for the long post and I think my issue is related to conversion between a variant type and Array type. Thanks for your help.

**p45cal** · 10-16-2013, 08:18 PM

try something along these lines (untested):

Code:

If (Worksheets(const_ParametersSheetName).InitialChkBox.Value = True) Then vSubTypes = "Initial,"
If (Worksheets(const_ParametersSheetName).InitialCloseOutChkBox.Value = True) Then vSubTypes = vSubTypes & "Initial Close-Out,"
'put your code to remove last comma here.
vSubTypes = Split(vSubTypes, ",")
Rng.AutoFilter Field:=8, Criteria1:=vSubTypes, Operator:=xlFilterValues