if your columns have the same number of values and you don't have to

COUNTIF for each column this is all you need:



With Range("A1:E1")

With .Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,

Operator:= _

xlBetween,

Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

End With

End With

Regards

Claus B.

--

Another clever little gem.

Thanks Claus.

Howard