MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Referring to a named range in XL

  Asked By: Holly    Date: Mar 13    Category: MS Office    Views: 5242

In the XL-sheet I have named the following:

New_rows =OFFSET(Data!$B$1;1;0;COUNT(Data!$B$2:$B$2004);4)

I want to refer to this range in VBA in order to copy the data like:


The Range("New_rows").Select does not work - how Do I higlight/select
the named range?

Should I write create a Range in VBA instead and populate this with the
same formula?




3 Answers Found

Answer #1    Answered By: Trae Thompson     Answered On: Mar 13

Let's work backwards.
You need to be able to select a range  in order to copy it.
your statement:
implies that in your SPREADSHEET, there is a Named Range
called "New_rows".
But, from your coding, that is not the case, you're trying to
set a VBA VARIABLE to be the range.
In that case:
should work (without the quotes)...
the quotes tell the compiler to use the literal value "New_rows"
instead of the value of the variable New_rows.
Now, working backwards... you need to set New_rows equal to a range.
Why are you using "offset"??
To use offset, you first must have a starting point, then supply the
number of rows and columns from that starting point.
It looks like you're trying to select the data in column B (not counting row 1)
The problem is, in VBA "Count" is a Property of all kinds of things.
It can be the number of Windows (all applications), the number of Workbooks, the
number of WorkSHEETS, the number of cells selected, and many more (I stopped
counting after 70 entries in Help).
Are you trying to get a count of the number of non-blank cells?
If so, Count isn't the way, because even if you get the syntax right, what you
GET is the
total number of cells whether they have data or not.. and the ;4 you added COULD
mean that
you were expecting to count through column F (B+4 = F)
Your code doesn't come close to explaining what you're trying to do.
Do you actually have the range hard-coded? ($B$2004) or is this just an example?
OK.. to give you a starting point.
Let's say:
You have a sheet called "Data"
you have data in columns B-F, rows 2-?? but not more than row 2004, with a
header in row 1.
You want to copy all of the data in B-F where there is a value in column B.
Here's how I would do it:

RowCount = Application.WorksheetFunction.CountA(Range("B2:B2004"))
Range(Cells(2, 2), Cells(RowCount, 5)).Select
Now, you COULD combine the two like:
Range(Cells(2, 2),
Cells(Application.WorksheetFunction.CountA(Range("B2:B2004")), 5)).Select
CountA is a Worksheet function, not a VBA function.
It returns the number of non-blank cells.
However, remember if there are blank cells mixed into your data, it won't count
If you wre wanting all of the rows between 2 and 2004, simply:
If you need more help, I'll need to know a bit more about your application.
Hope this helps,

Answer #2    Answered By: Rochelle Elliott     Answered On: Mar 13

Looks like the OFFSET() function may be using the wrong parameter delimiters
Try changing:
New_rows =OFFSET(Data!$B$1;1;0;COUNT(Data!$B$2:$B$2004);4)
to this with the [comma]:
New_rows =OFFSET(Data!$B$1,1,0,COUNT(Data!$B$2:$B$2004),4)

Answer #3    Answered By: Silvia Chapman     Answered On: Mar 13

Try replacing the semi-colons in your OFFSET formula with commas.

Didn't find what you were looking for? Find more on Referring to a named range in XL Or get search suggestion and latest updates.