Excel VBA to Create Data Validation List from Range with Filter

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.