Results 1 to 3 of 3

Thread: AutoFiltering Data COlumns

  1. #1
    Junior Member
    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    Post AutoFiltering Data COlumns

    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.
    Last edited by labkhand; 10-15-2013 at 05:42 PM. Reason: typo

  2. #2
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    93
    Rep Power
    11
    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
    Last edited by p45cal; 10-16-2013 at 08:35 PM.

  3. #3
    Junior Member
    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0
    Thanks very much. I was able to figure it out now.

Similar Threads

  1. Swapping (Rearranging) Multiple Columns of Data
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 13
    Last Post: 07-15-2014, 05:21 AM
  2. Move data from rows into columns for every unique value
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-13-2013, 03:02 AM
  3. Macro to copy data in specific Columns
    By Howardc in forum Excel Help
    Replies: 0
    Last Post: 04-19-2013, 10:42 AM
  4. Locking Columns data to be edited.
    By acsinha in forum Excel Help
    Replies: 1
    Last Post: 04-11-2013, 10:33 AM
  5. Swapping Multiple Columns of Data
    By jomili in forum Excel Help
    Replies: 5
    Last Post: 10-01-2012, 05:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •