Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Data Validation/List Question

    I am using a list for data validation from an existing table and the table has empty cells, which makes for a long dropdown box. For example:


    CLF-033.3
    CLF-035.3
    CLF-035.4
    CLF-035.5
    CLF-036.1


    FWF-017


    MBF-091


    PBF-011
    PBF-012

    Is there a way to remove the blank cells from the dropdown box? I donít want to do a copy/paste/sort to remove the blanks as we need to keep them in a non-alpha order. Copying table data to a named range without the blank cells would work, but I canít figure out how to do it.

    Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If you filter the list with blank cells, you can display only those non-blank cells, copy and paste them (including the header) to another column (I put them in F).
    Use that column as your data validation.

    Then, in data validation, use a list with this type of reference: =OFFSET($F$2,0,0,SUMPRODUCT(- -($F$2:$F$16<>"")),1)
    (I put the pasted list in the F column. This formula should remove the trailing blanks at the bottom of the list of 16.)
    (I assumed there was a header in F1)
    Last edited by kweaver; 2014-01-13 at 16:34.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts

    A Vb approach

    jepalmer,

    The code below will cycle through the validation list building a new validation list without blanks by assigning it to a variable string. It will then create the validation list for the selected cell by inserting the variable as a comma delimited list. The code can be used repeatedly to set up your sheet by changing the range for the validation list and/or selecting a new cell and running the code. I have included buttons to create and remove the data validation.

    HTH,
    Maud

    DataValidation1.png

    Place in a standard module:
    Code:
    Sub CreateValidation()
    'DECLARE AND SET VARIABLES
    Dim rng As Range
    Dim cell As Range
    Set rng = Worksheets("Sheet1").Range("D2:D16") 'CHANGE RANGE
    Dvalidation = ""
    '-------------------------------
    'FILTER BLANK CELLS FROM VALIDATION DATA SET
    For Each cell In rng
        If cell <> "" And Dvalidation = "" Then
            Dvalidation = cell
        ElseIf cell <> "" And Dvalidation <> "" Then
            Dvalidation = Dvalidation & ", " & cell
        End If
    Next cell
    '------------------------------
    'CREATE CELL VALIDATION    
    With ActiveCell.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Dvalidation
        End With
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-01-15 at 22:56. Reason: add file

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Creed (2014-03-09)

Posting Permissions

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