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
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
Code:
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