Drop-down filled dynamically from range how to clear drop-down if range empty

[capson]

I have a dynamic Drop-down being populated by the Headers from Range("G1:AZ1") when a button is clicked, there could be any number of headers in this range

If there are No headers in this range i.e. all cells are empty then I need to clear the drop-down of the values it has.

But I am having troubles doing this

Thank you for any help on this

Sub setupDV()
    Dim rSource As Range, rDV As Range, r  As Range, csString As String, v As String
    Dim c As Collection


    Set rSource = Sheets("Upload").Range("G1:AZ1")
    Set rDV = Sheets("List").Range("A2")
    Set c = New Collection
    csString = ""
    On Error Resume Next
    For Each r In rSource
        v = r.value
        If v <> "" Then
            c.Add v, CStr(v)
            If Err.Number = 0 Then
                If csString = "" Then
                    csString = v
                Else
                    csString = csString & "," & v
                End If
            Else
                Err.Number = 0
            End If
        End If
    Next r
    On Error GoTo 0
   
    With rDV.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=csString
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

 

[Fluff]

How about

Sub setupDV()
   Dim cl As Range, rDV As Range
   Dim St As String
   
   Set rDV = Sheets("List").Range("A2")
   With CreateObject("scripting.dictionary")
      For Each cl In Sheets("Upload").Range("G1:AZ1")
         If cl.Value <> "" Then .Item(cl.Value) = Empty
      Next cl
      St = join(.Keys, ",")
   End With
   With rDV.Validation
      .Delete
      If St <> "" Then
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=csString
         .IgnoreBlank = True
         .InCellDropdown = True
         .InputTitle = ""
         .ErrorTitle = ""
         .InputMessage = ""
         .ErrorMessage = ""
         .ShowInput = True
         .ShowError = False
      End If
   End With
End Sub

 

[mumps]

Try:

Sub test()
    If WorksheetFunction.CountA(Range("G1:AZ1")) = 0 Then
        Range("A2").Validation.Delete
    Else
        With Range("A2")
            With .Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$G$1:$AZ$1"
            End With
        End With
    End If
End Sub

 

[capson]

Thank you Mumps and Fluff both worked great

 

[Fluff]

Glad we could help & thanks for the feedback