Set named range from cells selected

Question

I have to set a named range for the last 2 rows with contents in them on a specific sheet. I am able to select those 2 rows with the contents i need in but am unable to set a named range from the selected cells. I was wondering if there was a way to set a named range from the selected cells.

I have tried recording a macro and adapting it to suit my needs but am unable to do so. As well as trying the code below using the cell references. But this needs to be run on a lots of files and different files will have different bottom rows.

Sub LastCell()

Dim MyNamedRng As Range

'Select Worksheet
Worksheets("Sheet1").Activate

'Select Last Used Cell In The Worksheet
 Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select

'Select Cell Above Last + All To The Left
    Range(Selection, Selection.Offset(-1, 0)).Select
    Range(Selection, Selection.End(xlToLeft)).Select

'Create Named Range
    Set MyNamedRng = Sheets("Sheet1").Range("a3:gk4")
      Names.Add Name:="KeyData", RefersTo:=MyNamedRng

 End Sub

Answer 1

This will find the last used column in the relevant rows.

Sub LastCell()

Dim r1 As Range, r2 As Range

Worksheets("Sheet1").Activate

Set r1 = Cells.Find(What:="*", After:=[A1],SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not r1 Is Nothing Then
    If r1.Row > 1 Then
        Set r2 = r1.offset(-1).resize(2).Entirerow.Find(What:="*", After:=Cells(r1.Row, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        'Use line below if last used column is not in the named range
        'Set r2 = Cells.Find(What:="*", After:=Cells(r1.Row, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        Range(Cells(r1.Row - 1, "A"), Cells(r1.Row, r2.Column)).Name = "KeyData"
    End If
End If

End Sub

Read this for the perils of using Select.