Formula in VBA
 

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

Formula in VBA
 

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

Formula in VBA
 

One mo

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 wrote:

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

--

Dave Peterson

Formula in VBA
 

Thank you both
Sandy

"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