Excel - Data Validation list from filtered table

Question

I am looking for a way to make a data validation list pull from a filtered table. Ex. I have a sheet called customers with a table named CustomerList column A=Customer B=Address C=City D=State On another sheet named Quote I have cell C13 for customer name which has a data validation list to a dynamic range of Sheet Customers column A Customer. In my list it shows all 1800 customers even if i filter the table to show only customers in a certain state. I would like to be able to set filters on the table to sort my customers and have my data validation list only show the customers shown on the filtered list. For the life of me I can not figure this out. Any help would be greatly appreciated. TIA.

Answer 1

In sheet Customers, pick some cell and enter:

=SUBTOTAL(103,A:A)

This formula will be re-calculated every time the filter is changed for column A.

In the Customers worksheet code area, install the following event macro:

Private Sub Worksheet_Calculate()
    Call makeDV
End Sub

In a standard module, install the following code:

Public DVList As String

Public Sub makeDV()

    Dim A As Range, r As Range
    Dim c As Collection, v As Variant
    Set c = New Collection
    Set A = Intersect(Range("A2:A" & Rows.Count), ActiveSheet.UsedRange).Cells.SpecialCells(xlCellTypeVisible)
    DVList = ""

    On Error Resume Next

    For Each r In A
        v = r.Value
        If v <> "" Then
           c.Add v, CStr(v)
           If Err.Number = 0 Then
               DVList = DVList & "," & v
           Else
            Err.Clear
           End If
        End If
    Next r

    If Len(DVList) <> 0 Then DVList = Mid(DVList, 2)
    On Error GoTo 0

    Dim Quote As Worksheet
    Set Quote = Sheets("Quote")

    With Quote.Range("C13").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=DVList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Whenever the filter is modified on sheet Customers, the data validation for cell C13 on sheet Quotes will up updated.