We help IT Professionals succeed at work.
Get Started

Excel VBA: Named range can't be assigned to range object

804 Views
Last Modified: 2016-02-11
Objective

To assign the rows from a named range to a range object so it can be processed.
The named range is created by another earlier function.

Issue

I have the following code (an abbreviated extract showing only the relevant code):

    Dim rngOrigDataRows As Range
Dim blnInputSheetRowsFiltered  as Boolean
    
    ' Init. var.
    blnInputSheetRowsFiltered = blnWorkbookNameExists(wkbInputModule, INPTMOD_NMDRNG_ROWFILTER_RSLTROWS)
    
    With wkbInputModule
    
        ' Get the origonal rowset to work on
        If blnInputSheetRowsFiltered Then
            Set rngOrigDataRows = .Names(INPTMOD_NMDRNG_ROWFILTER_RSLTROWS).RefersToRange
        Else
            Set rngOrigDataRows = .Names("InputSheetDataRows").RefersToRange
        End If

Open in new window


When the named range 'INPTMOD_NMDRNG_ROWFILTER_RSLTROWS' does exist the code that assigns it to a range fails with the following error:

1004::Application-defined or object-defined error

The contents of the named range is as follows:

wkbInputModule.Names(INPTMOD_NMDRNG_ROWFILTER_RSLTROWS).RefersTo = "=InputSheet!$35:$37,InputSheet!$41:$48,InputSheet!$52:$71,InputSheet!$73:$84,InputSheet!$87:$94,InputSheet!$97:$126,InputSheet!$129:$149,InputSheet!$153:$160,InputSheet!$164:$183,InputSheet!$185:$196,InputSheet!$199:$228,InputSheet!$231:$250"

Open in new window


There are 12 areas to the range.

 If I have only 11 areas then I get no error.

Attempts to resolve

I have even tried changing the 12th and final area to point to different rows but it doesn't help.
I have also tried ensuring that the 'InputSheet' worksheet is not protected (just in case) but this had no effect.
I created a new workbook in a new session of Excel and renamed on of the worksheets 'InputSheet' and used the above 'RefersTo' string to create a new range and the error still occurred.

Question

I have never come across this before. To my knowledge ranges can consist of more than 600 areas (no idea why you would want that) but that is far far more than I am using.

My VBA project uses literally hundreds of named ranges over about half-a-dozen workbooks and I have never hit this issue with large row multi-area selections.

What can this be and how can I work around it ?
Comment
Watch Question
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This problem has been solved!
Unlock 3 Answers and 21 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE