Syntax for named range

[mtterry]

Seems like I'm missing something simple here to name a dynamic range. I'm trying to do this without creating a variable for the last row, etc.

With wsLists.Range("A1")
.Names.Add Name:="supplierSitesRng", RefersTo:=Range(.Offset(1, 0), .End(xlDown))
End With

 

[DanteAmor]

Try

    With wsLists.Range("A1")
        ActiveWorkbook.Names.Add Name:="supplierSitesRng", RefersTo:=Range(.Offset(1, 0), .End(xlDown))
    End With

Or:

    With wsLists.Range("A1")
        wsLists.Names.Add Name:="supplierSitesRng", RefersTo:=Range(.Offset(1, 0), .End(xlDown))
    End With

 

[mtterry]

That worked, thank you. Can you tell me why I needed to add the 'wsLists' again before the '.Names.Add'?

 

[DanteAmor]

That worked, thank you. Can you tell me why I needed to add the 'wsLists' again before the '.Names.Add'?

It is because the With statement considers Range("A1"), otherwise it would be like this:

    With wsLists
        .Names.Add Name:="supplierSitesRng", RefersTo:=Range([B].Range("A1")[/B].Offset(1, 0), [B].Range("A1")[/B].End(xlDown))
    End With