On Saturday, May 24, 2014 1:05:32 PM UTC-7, GS wrote:
Other than the obvious exercise in array handling this demonstrates,

I'm curious why you don't just assign Range("F1:F10") as the DV source

list for Range("A1:A10") in one shot! What am I missing here?



--

Garry


Hi Garry,

Here is the question I was responding to in my first post here.

Regarding the creation of a list (Insert or delete a drop-down list - Excel), is it possible to do this as a macro?


After posting Claus's solution to my query here, this is the next question I am trying to respond to.

Is it possible to create the drop down values within VB, or will I always have to reference cells in a worksheet?

Followed by this.

How could I tweak this to populate a column in specific worksheets for rows that have values? I'm using a data connection to get data from an external file and have set the properties to insert cells for new data, delete unused cells. After the data refresh is complete I'd like to apply your solution for each worksheet in column 'S' for any rows that have data.

For the "create the drop down values within VB..." question, here is where I am so far with an Object-Defined error.

Sub DV_Test()

Dim MyArray As Variant

MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

'With Range("A1:A5").Validation '/ Puts DV's in A1 to A5 all with F1:F10 list.
With Range("B1").Validation '/ Puts DV in B1 with the F1:F10 list.
'With Selection.Validation '/ Put DV in selected cell/s with the F1:F10 list.
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=MyArray

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub


I have no idea what the last query is looking to do.

Howard