x = Activesheet.Evaluate("ROWS(C2:C300)-COUNTBLANK(C2:C300)")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sandy" wrote in message
...
I currently have the following in a macro

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

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

where D2 contains the following formula

"=(ROWS(C2:C300)-COUNTBLANK(C2:C300))"

what I would like to do is have the whole thing contained in the code viz:

"Dim x As Integer
x = (ROWS(C2:C300)-COUNTBLANK(C2:C300))

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

This doesn't work though - any advice

Thanks Sandy