Dynamic Named Range error: 'Run-Time Error '1004 - Method 'Range' of object '_Worksheet' failed'

Question

I am trying to name a Dynamic Range in Worksheet HDaER.

The range should be from .Cells(3, 2) to HDaERCloseLR (Last Row) and HDaERCloseLR (Last Column) of the populated area.

So far I have this:

    1    Dim HDaER As Worksheet    
    2    Dim HDaERCloseLR As Integer 
    3    Dim HDaERCloseLC As Integer 
    4    Dim HDaERCloseDNR As Range
    5    Dim HDaER As String    
    6    Set HDaER = Sheets("HDaER")
    7    
    8    With HDaER.Cells
    9        HDaERCloseLR = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, _
    10       SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
    11          
    12       HDaERCloseLC = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, _
    13       SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 
    14        
    15       Set HDaERCloseDNR = HDaER.Range(HDaER.Cells(3, 2) & _
    16       HDaER.Cells(HDaERCloseLR, HDaERCloseLC))
    17   End With
    18        
    19        Scope.Names.Add Name:=HDaERClose, RefersTo:=HDaERCloseDNR
    20    
    21        HDaERClose.Select

I get

Run-Time Error '1004' - 'Method 'Range' of object '_Worksheet' failed'

on line 15, where I have Set HDaERCloseDNR = HDaER.Range(HDaER.Cells(3, 2) & HDaER.Cells(HDaERCloseLR, HDaERCloseLC)).

I used the .Select command for the Named Range HDaERClose at line 21 for a test of the code, but never go there.

After applying the changes suggested in the comments and answers, I got this:

I got to select the range, but what I need is the range until the last row (105 in the example) and the last column (E in the example) of the first data set.

The data from row 105 on and column E on should be excluded.

I do not see any where you assign a value to `HDaERClose`. I think you want to enclose it in quotes `"HDaERClose"` then you would call it with `Range("HDaERClose").Select` — Scott Craner, Mar 08 '18 at 01:00
Use `Option Explicit` at the top of your modules to (help) prevent errors arising from undeclared variables per @ScottCraner, and show the rest of your code. From what you've given, `Scope` object is not instantiated/assigned. — David Zemens, Mar 08 '18 at 01:02
@ ScottCraner: I missed that line when I copied the code. I made an edit in the code above - added `Dim HDaERClose As String` in line 5. — I. Я. Newb, Mar 08 '18 at 01:25
@ ScottCraner: Your suggestion for using `with hdaer.cells(3,2).currentregion` is to replace `With HDaER.Cells`, right? — I. Я. Newb, Mar 08 '18 at 01:28
@ScottCraner: Could you, please, post your suggestion about the `With` statement as an Answer, so I can mark it as one? Thank you guys. — I. Я. Newb, Mar 08 '18 at 01:34

score 4 · Accepted Answer · answered Mar 08 '18 at 00:47

4

I think you meant to use a comma (,) instead of the ampersand (&).

15       Set HDaERCloseDNR = HDaER.Range(HDaER.Cells(3, 2) , _
16          HDaER.Cells(HDaERCloseLR, HDaERCloseLC))

answered Mar 08 '18 at 00:47

K.Dᴀᴠɪs

9,945
11
33
43

@K.Davis: Newbie mistake. That got me trough line 15, but now I get a 'Run-Time Error 424: Object Required on line 19, where I have `Scope.Names.Add Name:=HDaERClose, RefersTo:=HDaERCloseDNR`. – I. Я. Newb Mar 08 '18 at 00:51
@David Zemens: What do you mean? What other way would you suggest? – I. Я. Newb Mar 08 '18 at 00:52
2

@I.Я.Newb It's not clear from the code you've provided whwat `Scope` is. Presumably, from the error message, it's an undefined/unassigned object variable. Also note that you should use `Long` instead of `Integer` for your row/column variables, as the number of rows in a spreadsheet vastly exceeds the limit of `Integer` data type. – David Zemens Mar 08 '18 at 00:56
@David Zemens: I tried defying Last Row and Last Column the traditional way with `HDaERCloseLR = HDaER.Cells(HDaER.Rows.Count, 2).End (xlUp).Row` and Last Column HDaERCloseLC = HDaER.Cells(HDaER.Columns.Count, 2).End (xlUp).Column`, but gave me the same error on the Last Row defiance. – I. Я. Newb Mar 08 '18 at 00:57
1

See [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920?s=1|0.0000#11169920) for what is basically the definitive way to find the "last" cell in a range/sheet/etc. – David Zemens Mar 08 '18 at 00:58
@David Zemens: My last comment was a long shot of what you meant in your former comment, that I see you deleted for the reason you mentioned. – I. Я. Newb Mar 08 '18 at 01:00
@K.Davis: I changed the `Scope` line to `HDaER.Names.Add` – I. Я. Newb Mar 08 '18 at 01:20

score 2 · Answer 2 · answered Mar 08 '18 at 00:51

In addition to the solution posed by K.Dᴀᴠɪs, I would suggest the following modification to ensure accurate returns,

9        HDaERCloseLR = .Find(What:="*", AFTER:=.CELLS(1,1), LookIn:=xlFormulas, LookAt:=xlPart, _
10       SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
11          
12       HDaERCloseLC = .Find(What:="*", AFTER:=.CELLS(1,1), LookIn:=xlFormulas, LookAt:=xlPart, _
13       SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

I. Я. Newb · Answer 3 · 2018-11-09T19:55:28.640

After applying all recommended corrections to the initial code, the final version is:

1    Dim HDaER As Worksheet
2    Dim HDaERCloseLR As Long
3    Dim HDaERCloseLC As Long
4    Dim HDaERReturnLR As Long
5    Dim HDaERReturnLC As Long
6    Dim HDaERCloseDNR As Range
7    Dim HDaERReturnsDNR As Range
8
9    Set HDaER = Sheets("HDaER")
10    
11   With HDaER.Cells(3, 2).CurrentRegion
12        HDaERCloseLR = .Find(What:="*", After:=HDaER.Cells(1, 1),  LookIn:=xlFormulas, LookAt:=xlPart, _
13        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
14
15        HDaERCloseLC = .Find(What:="*", After:=HDaER.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
16        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
17        
18        Set HDaERCloseDNR = HDaER.Range(HDaER.Cells(3, 2), _
19        HDaER.Cells(HDaERCloseLR, HDaERCloseLC))
20    End With
21
22    HDaER.Names.Add Name:="HDaERClose", RefersTo:=HDaERCloseDNR
23    
24    Range("HDaERClose").Select

The output from the code is:

(selected range represents Dynamic Named Range HDaERClose)

APPLIED CORRECTIONS

From @K.Davis' Answer:

I think you meant to use a comma (,) instead of the ampersand (&).
15  Set HDaERCloseDNR = HDaER.Range(HDaER.Cells(3, 2) , _
16  HDaER.Cells(HDaERCloseLR, HDaERCloseLC))

Replaced the mistaken & with , .

After the correction, there was no issue with line 15 (line 18 in the final version) , so that was the Answer that I was looking for.

From @Japeed's Answer:

In addition to the solution posed by K.Dᴀᴠɪs, I would suggest the following modification to ensure accurate returns,

9        HDaERCloseLR = .Find(What:="*", AFTER:=.CELLS(1,1), LookIn:=xlFormulas, LookAt:=xlPart, _
10       SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
11          
12       HDaERCloseLC = .Find(What:="*", AFTER:=.CELLS(1,1), LookIn:=xlFormulas, LookAt:=xlPart, _
13       SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Added After:=HDaER.Cells(1,1) to the HDaERCloseLR and HDaERCloseLC variable definitions, in lines 12 and 15 in the latter version and 9 and 12 in the former version, respectively.

From @ScottCraner's Comment:

I do not see any where you assign a value to HDaERClose. I think you want to enclose it in quotes "HDaERClose" then you would call it with Range("HDaERClose").Select

I closed HDaERClose in quotes and replaced Scope with HDaER, referring to Worksheet("HDaER"), when assigning it as Range Name in line 22 in the latter code version as follows:

HDaER.Names.Add Name:="HDaERClose", RefersTo:=HDaERCloseDNR

That sorted the issue with this line.

From @ScottCraner's comment:

(In regards of Edit 1)

Then you want with hdaer.cells(3,2).currentregion

I changed the Withfunction from With HDaER.Cells to With HDaER.Cells(3,2).CorrentRegion and calibrated the reference to only the Range that I needed.

A very helpful link by @ScottCraner in regards of defining last row and last column can be found here.

Much appreciation to all who participated in solving this Question. I hope that it will help others too.

Dynamic Named Range error: 'Run-Time Error '1004 - Method 'Range' of object '_Worksheet' failed'

3 Answers3

The output from the code is:

APPLIED CORRECTIONS