0

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
mbarbour
  • 3
  • 1

1 Answers1

0

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.

SJR
  • 22,986
  • 6
  • 18
  • 26
  • That seemed to work on some of the files but some go out to KA, so how could i incorporate that into the code? – mbarbour Oct 25 '19 at 12:06
  • The edit on creates the named range as A3 and A4 it doesn't stretch across to the last cell used. – mbarbour Oct 25 '19 at 12:35
  • Is the last column used in a row not contained in the named range? If so, you can try `Set r2 = Cells.Find(What ...`. – SJR Oct 25 '19 at 12:39