Hi

I think I have cracked the first bit

Sub TestListBox()

Range("G2:G31").Select
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Dim x As Integer
x = Sheets("Validation Lists").Range("K1").Value

ThisWorkbook.Names.Add Name:="ListBoxCourse", _
RefersTo:=Range(Cells(2, 9), Cells(x + 1, 9)), Visible:=True

Range("H1").Select

End Sub

Not got a clue about A-Z ing the list box but this seems to do the basic
job.

Sandy

"Sandy" wrote in message
...
I have a list of data (non-numeric) in column I (with a heading). This list
varies in the number of entries there are.

If I use COUNTA to establish the number of non-blank cells I always get an
error; eg I have 6 entries at the moment which is correctly given by the
formula in K1:- "=(ROWS(I2:I50)-COUNTBLANK(I2:I50)) whereas COUNTA arrives
at afigure of 28????

Anyway, if I use the list as is, my listbox contains 22 blank entries. I
would like to use the result from K1 to set the named range "ListBox" to
I2:I7, in other words the 6 non-blank entries. The non-blank cells always
appear at the top of the column as a result of manipulation, prior to
pasting to column I.

An added bonus would be to set them A-Z too, :-)

Any help greatly appreciated

Sandy