Dynamic Named Range count

  • Thread starter Thread starter Matt Jensen
  • Start date Start date
M

Matt Jensen

Howdy
When I do a count of a dyanmic named range I get 1, when I do it for a
static named range I get the correct count of the range.
This is my code:

Private Sub UserForm_Initialize()
Dim DirectorateCount As Long
Dim rgDirectorateListSource As Range

Set rgDirectorateListSource =
Worksheets("Data-Directorates").Range("DirectorateRange")
DirectorateCount = rgDirectorateListSource.count
End Sub

Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong?
Thanks
Matt
 
I do not see anything wrong. Is your named range DirectorateRange correctly
specified?
 
It is defined like this
=OFFSET('Data-Directorates'!$A$1,0,0,COUNTA(DataDirectorates!$A:$A),1)
Cheers
Matt
 
My apologies
Typos were the problem, as can be seen in name definition formula.
Cheers
Matt
 
Matt,

As ever, it works fine for me. How do you define the dynamic range?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Your formula refers to two separate sheets

Data-Directorates
DataDirectorates
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - counting cells by colors and names to it in two different rows. 0
Post my named ranges in column A 10
Inaccurate count with code 6
Named Range maker code 9
Array coding type mismatch 6
How to hide password? 1
Error populating a list box 1
Trying to run excel vba code when cell value changes from empty to entered value. 1

Back
Top