Dynamic filtered range in validated cell

Question

I use excel italian so vba accepts naturally english commands, sheet accepts nationalized (italian) formulas

For non filtered table i use the following code to empty cell from list and repopulate cell with a new list:

first sub

Sub ComboClear(s As String)
 's is the cell containing combo
    Range(s).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Second sub:

Sub ComboRefresh(s As String, mycol As String, myrow As Integer)

 's is the cell containing combo
 'mycol is the column where are localized the combo values
 'myrow is the row start values of combo

     Dim nrows As Integer
     nrows = Range("A" & Rows.Count).End(xlUp).Row
     Range(s).Select
     With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$" & mycol & "$" & myrow & ":$" & mycol & "$" & nrows
        .IgnoreBlank = True
       .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
       .InputMessage = ""
        .ErrorMessage = ""
       .ShowInput = True
       .ShowError = True
    End With
    Range(s).Value = ""
End Sub

I have problems to try best solution if for filtered table, where not all rows are visible first sub creates dynamic range and the code works

Sub CreateList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim visibleCells As Range
    Dim validationRange As String
    
    Set ws = ActiveSheet
    Set rng = ws.Range("A5:A11")
    
    ' delete FilteredRange if exists
    On Error Resume Next
    ActiveWorkbook.Names("FilteredRange").Delete
    On Error GoTo 0
    
    ' Create a dynamic range with only visible cells
    '===============================================
    On Error Resume Next
    Set visibleCells = rng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleCells Is Nothing Then
        validationRange = ""
        For Each cell In visibleCells
            If validationRange = "" Then
                validationRange = cell.Address
            Else
                validationRange = validationRange & "," & cell.Address
            End If
        Next cell
        
        ' dynamic range name Filtered Range
        ws.Names.Add Name:="FilteredRange", RefersTo:="=" & validationRange

     ' Set validation for specifica cell with dynamic range
     '=====================================================
        ' Imposta la convalida dei dati sulla cella A3
        With ws.Range("A3").Validation
            .Delete ' Rimuove eventuali convalide esistenti
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween,            Formula1:="=FilteredRange"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    End If
End Sub

I have error in linecode:

.Add Type:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=xlBetween, Formula1:="=FilteredRange"

I think to have another solution: copy only filtered rows in other part of the worksheet and validating cell with the continuos range, but i prefer to not add other data to the sheet.

In https://stackoverflow.com/questions/44505625/selecting-dynamic-filtered-range-in-vba i see this code that reachs probably same result that firs part of my code

ActiveSheet.Range("A2:J"&lRow).SpecialCells(xlCellTypeVisible)

Dynamic filtered range in validated cell

Answers (1)

Related Questions