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