Formula in VBA

[Sandy]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

One more:

dim myRng as range
dim x as long
set myrng = activesheet.range("c2:c300")
x = myrng.rows.count - application.countblank(myrng)

'or
x = myrng.Cells.count - application.countblank(myrng)

 

[Sandy]

Thank you both
Sandy