Forum Discussion

Viorica000's avatar
Viorica000
Copper Contributor
May 11, 2023

Excel VBA to Create Data Validation List from Range with Filter

Hello everyone,

I have the following situation:
I have to add a data validation from a range but applying a filter

 

My Data Validation must be composed from the following list (Activity) only when the column TYPE = 1

how to apply a filter in the VBA for a data validation?

 

 

 

 

 

 

 

Sub PopulateFromANamedRange()

Range("A18").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Activity"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True

End Sub

 

 

 

thanks a lot

 

 

 

 

BI & Data Analysis
excel
Formulas and Functions
Macros and VBA

4 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 11, 2023

    Viorica000 

    You can use the AutoFilter method to filter the range before creating the data validation.

    Here's an example code that should work for your situation:

    Sub PopulateFromANamedRange()
    Dim ws As Worksheet
    Dim filterRange As Range, validationRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1") 'change the sheet name to your sheet
    
    'set the range to be filtered and the validation range
    Set filterRange = ws.Range("A1:B4")
    Set validationRange = ws.Range("A1:A4")
    
    'filter the range to show only rows where type = 1
    filterRange.AutoFilter Field:=2, Criteria1:=1
    
    'create the data validation from the filtered range
    With ws.Range("A18").Validation
        .Delete 'remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Formula1:="=" & filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Error"
        .InputMessage = ""
        .ErrorMessage = "Please provide a valid input"
        .ShowInput = True
        .ShowError = True
    End With
    
    'remove the filter from the range
    filterRange.AutoFilter Field:=2
    
End Sub

    I hope this helps!

    • Viorica000's avatar
      Viorica000
      Copper Contributor
      May 11, 2023

      HI,NikolinoDE 

       

      thanks you very much, but they give me this error:

       

       

       

       

      Sub PopulateFromANamedRange()
    Dim ws As Worksheet
    Dim wss As Worksheet
    Dim filterRange As Range, validationRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set wss = ThisWorkbook.Sheets("Sheet2")
    
    'set the range to be filtered and the validation range
    Set filterRange = ws.Range("A1:F62")  'F has the Criteria=1 (Type)
    Set validationRange = ws.Range("C1:C62") 'C has the value that need filter (Activity)
   
   
    'filter the range to show only rows where type = 1
    filterRange.AutoFilter Field:=6, Criteria1:=1
    
    'create the data validation from the filtered range
    With wss.Range("Activity").Validation
        .Delete 'remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Formula1:="=" & filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Error"
        .InputMessage = ""
        .ErrorMessage = "Please provide a valid input"
        .ShowInput = True
        .ShowError = True
    End With
    
    'remove the filter from the range
    filterRange.AutoFilter Field:=6
    
End Sub

      :

       

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        May 11, 2023

        Viorica000 

        The error "1004" is a generic error in Excel, maybe related to the following line of code:

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
     Formula1:="=" & filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Address

        The error can occur if the range specified in filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Address does not contain any visible cells, or if there are not enough visible cells to create a valid data validation list.

         

        To fix this error, you can add some error handling code to check if there are any visible cells in the filtered range before creating the data validation list.

        Here's an example:

        'check if there are any visible cells in the filtered range
If Application.WorksheetFunction.Subtotal(103, filterRange) > 1 Then
    'create the data validation from the filtered range
    With wss.Range("Activity").Validation
        .Delete 'remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Formula1:="=" & filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Error"
        .InputMessage = ""
        .ErrorMessage = "Please provide a valid input"
        .ShowInput = True
        .ShowError = True
    End With
Else
    MsgBox "No visible cells to create data validation list.", vbExclamation
End If

        This code checks if there are any visible cells in the filtered range using the Subtotal function. If there are visible cells, it creates the data validation list as before. If there are no visible cells, it displays a message box informing the user that there are no visible cells to create the data validation list.

         

Resources