It worked ok for me.

But I did change the way that range was obtained.

Instead of starting with N4 and working down, I anchored at N4, but worked my
way up from the bottom.
With .Range("N4", .Cells(.Rows.Count, "N").End(xlUp))
You may want:
With .Range("N4", .Range("N4").end(xldown))

In fact, if you don't know how many rows you need, but you want to match the
number of used rows in a different column, you may want:

With .Range("N4:N" & .Cells(.Rows.Count, "A").End(xlUp).Row)

This used column A to determine the last row (starting from the bottom up
<vbg).



Nico wrote:

Thanks Dave! That works, but only for the first cell. Why isn't it copying
for the rest of the range (from N4 down)?

Thanks!

"Dave Peterson" wrote:

So the list is in a different workbook, right?

If that's true, then I'd start he
http://contextures.com/xlDataVal05.html

Option Explicit
Sub testme()

Dim Master As String
Dim TransClustFile As String
Dim myRng As Range
Dim myMasterName As String
Dim myListName As String

Master = "book1.xls"
TransClustFile = "Book2.xls"

myMasterName = "ResponseList"
myListName = "myList"

With Workbooks(Master).Worksheets("ResolutionCodesEN")
'in case the range can grow
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
myRng.Name = "ResponseList"
End With

With Workbooks(TransClustFile).Worksheets("sheet1")
.Names.Add Name:="MyList", _
RefersTo:="='" & Master & "'!" & myMasterName

With .Range("N4", .Cells(.Rows.Count, "N").End(xlUp))
With .Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & myListName
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.ErrorMessage = "Please select from the drop down menu"
.ShowInput = True
.ShowError = True
End With
End With
End With

End Sub





Nico wrote:

I tried that, but I'm still getting an error. Perhaps you can see where I'm
going wrong?

[...]

Windows(Master).Activate
Sheets("ResolutionCodesEN").Select
Range("A1").Select
ActiveWorkbook.Names.Add Name:="ResponseList",
RefersToR1C1:="=ResolutionCodesEN!R1C1:R16C1"
Sheets("BranchEN").Select
Windows(TransClustFile).Activate

'Inserts drop down list for New Resolution Code
Range("N4").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ResponseList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.ErrorMessage = "Please select from the drop down menu"
.ShowInput = True
.ShowError = True
End With

[...]

Thanks!

"Dave Peterson" wrote:

Is there a reason you don't put the list in a range on a worksheet?

Nico wrote:

Hello,

I'm trying to create a combo list in Excel with the following VBA code, but
it cuts off after only eight lines. Any suggestions?

Columns("N:N").Select
With Columns("N:N")
Selection.Locked = False
.Validation.Delete
.Validation.Add xlValidateList, , , "Example 1, Example 2, Example
3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
10, Example 11, Example 12, Example 13, Example 14"
Cells.EntireColumn.AutoFit
End With

Unfortunately it also cuts off some lines (here listed as "Example X") which
are long. Is there a character limit? I can't find any reference to one, but
I'm not sure why else it would cut off?

Thanks!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson