Inaccurate output of Dynamic Named Range

Question

I am using the exact same code to name a dynamic range in two workbooks. I use Workbook TestBook1 for testing the code before implementing it to the body of the general code in Workbook 111.

The code is:

    Dim HDaER As Worksheet
    Dim HDaERReturnLR As Long
    Dim HDaERReturnLC As Long
    Dim HDaERReturnsDNR As Range
    Dim HDaERReturns As String

    Set HDaER = Sheets("HistoricalDataandExcessReturns")

        With HDaER.Cells(108, 2).CurrentRegion
            HDaERReturnLR = .Find(What:="*", After:=HDaER.Cells(107, 1), _
            LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

            HDaERReturnLC = .Find(What:="*", After:=HDaER.Cells(107, 1), _
            LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    Set HDaERReturnsDNR = HDaER.Range(HDaER.Cells(108, 2), _
            HDaER.Cells(HDaERReturnLR, HDaERReturnLC))
        End With

        HDaER.Names.Add Name:="HDaERReturns", RefersTo:=HDaERReturnsDNR

        Range("HDaERReturns").Select

The output that I get in TestBook1 is ACCURATE:

And the output that I get in Workbook 111 is INACCURATE:

What am I doing wrong?

Answer 1

The specification After:=HDaER.Cells(...) appears as starting point... In this case I used After:=HDaER.Cells(107, 1), but I have the header "Volatility" in HDaER.Cells(105, 1), which means that specifying the start cell for the range with the line With HDaER.Cells(108, 2).CurrentRegionand including another specification for direction SearchDirection:=xlPrevious leads to marking HDaER.Range(HDaER.Cells(108, 2), HDaER.Cells(105, 1)) as the dynamic range to be named.

The code with the accurate output is:

Dim HDaER As Worksheet
Dim HDaERReturnLR As Long
Dim HDaERReturnLC As Long
Dim HDaERReturnsDNR As Range
Dim HDaERReturns As String

Set HDaER = Sheets("HistoricalDataandExcessReturns")

With HDaER.Cells(108, 2).CurrentRegion
            HDaERReturnLR = .Find(What:="*", After:=HDaER.Cells(105, 1), _
            LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

            HDaERReturnLC = .Find(What:="*", After:=HDaER.Cells(105, 1), _
            LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    Set HDaERReturnsDNR = HDaER.Range(HDaER.Cells(108, 2), _
            HDaER.Cells(HDaERReturnLR, HDaERReturnLC))
        End With

        HDaER.Names.Add Name:="HDaERReturns", RefersTo:=HDaERReturnsDNR

        Range("HDaERReturns").Select

Referring to HDaER.Cell(105, 1) as the starting point with the specification After:=HDaER.Cells(105, 1), leads to the accurate .Range selection.

To answer @DisplayName's comment and Answer:

Good idea, bad output...

MsgBox Last Row displays this result:

MsgBox Last Column displays this result:

Output of code (as I previously answered):

Exact code input:

Answer 2

your whole code could collapse to the following

With Sheets("HistoricalDataandExcessReturns").Cells(107, 1)
    With .Parent.Range(.End(xlDown), .End(xlToRight))
        .Parent.Names.Add name:="HDaERReturns", RefersTo:=.Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
    End With

    .Range("HDaERReturns").Select
End With