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.

Answer 1

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))

Answer 2

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 

Answer 3

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

---

1. 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.

2. 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.

3. 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.

4. 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.