Selecting a named range using VBA

[newhousekk]

Hello,
I am trying to use a named range for a number of functions within an Excel worksheet. My first step is to clear subtotals and previous content from the area of the named range. I have been receiving the error directly below at the first line of code where I attempt to set the range. I have tried several different approaches and keep hitting a wall. I will also be wanting to sort and subtotal the named range in future code. I would appreciate any help.
Thanks
Karen
Run Time error 424 - Object Required

Dim myrng As Range
'Step 1: Clear subtotals and clear previous content
    Set myrng = (MF_Data_2011)
    Worksheets("2011 MF Data").myrng.Select
    Selection.RemoveSubtotal
    myrng.ClearContents
    Set myrng = (NFF_Data_2011)
    Worksheets("2011 Frozen Data").myrng.Select
    Selection.RemoveSubtotal
    myrng.ClearContents
    Set myrng = (MF_Kashi_2011)
    Worksheets("2011 MF Kashi").myrng.Select
    Selection.RemoveSubtotal
    myrng.ClearContents

 

[Smitty]

Welcome to the Board!

Try:

Set myrng = Range("MF_Data_2011")

HTH,

 

[newhousekk]

Thanks for looking at it, Smitty. I tried your suggestion and received the Run-time error 1004, Method 'range' of object '_global' failed. Next suggestion?

 

[tushiroda]

I'm going to guess here that the named range does not exist on the sheet for some reason. Possibly it has been removed or you have a typo in the named range. hit F5 to see your named ranges to check.

 

[newhousekk]

I checked the named ranges, tushiroda. The name is correct. Would it matter that the named range is defined by an Offset formula that is flexible?

=OFFSET('2011 MF Data'!$A$1,0,0,COUNTA('2011 MF Data'!$A:$A),6)

I look forward to more ideas, myself, the carpool is waiting. Perhaps I will awake in the morning with an epiphany.

 

[tushiroda]

the dynamic range is definitely something to mention though I tested it out and it still works for me. The only way I've been able to generate your error message is to delete the named range and run it. I would trouble shoot by starting a new sub with just the first two lines and go from there. use option explicit too.

not sure what else to suggest

Sub test()
Dim blah As Range

Set blah = Range("testRange")

End Sub

 

[newhousekk]

Thanks for your response, Tushiroda. I apologize for not mentioning the dynamic range. I am new to the forum and will do better. I will give your suggestion a try this morning.

 

[Catalin_Lazar]

I had the same problem ( I used VBA 6.5). I managed to solve it replacing the "$" signs from calculated address (with OFFSET). I used the VBA function REPLACE, I suggest:

myrng =RANGE(TRIM(REPLACE(RANGE("MF_Data_2011"),"$","")))

TRIM function may be skipped in this case

Sorry for the late post, but I am now for the first time on this forum.

I checked the named ranges, tushiroda. The name is correct. Would it matter that the named range is defined by an Offset formula that is flexible?

=OFFSET('2011 MF Data'!$A$1,0,0,COUNTA('2011 MF Data'!$A:$A),6)

I look forward to more ideas, myself, the carpool is waiting. Perhaps I will awake in the morning with an epiphany.