"Simple" Select Last Row & Last Column & Name Range

[Shiseiji]

I've found dozens of almost answers, but am too ignorant to take the pieces to do what I want. I need to find the last row and the last column and name the range.

Many, many postings show getting the last row, or the last column, but not how to use both in the expression. And cautions against UsedRange . . .

I use this often,

Selection.AutoFill Destination:=Range("E2:E" & LastRow)

but have no idea how to use both the LastRow and LastCol . . .

All of Row 1 has headers. No blank/null cells.
All of Column "A" has entries. Again, no blank/null cells.
No formatting
No skipped rows or columns

My "almost there" code, I know I'm not using the LastRow and LastCol properly, but no idea how they should be used.

Sub LRowLColRng()
    Dim thiswksht    As Activesheet
    Dim LastRow      As Long
    Dim LastCol       As Integer
    Dim rng            As Range


    Set thiswksht = ActiveSheet
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    rng = (LastRow & LastCol) 'broken . . . 
    rng.Select
    Selection.Name = "tbl_P"
End Sub

As always,
TIA

Ron

 

[Norie]

Ron

What range do you actually want to select/name?

If it's just the single cell in the last row of the last column you could use Cells(LastRow, LastCol)

Set rng = Cells(LastRow, LastCol)

rng.Name = "tbl_P

If you wanted to select from the first cell in a range, let's say that's A2, to the last row/column you could use this.

Set rng = Range("A1", Cells(LastRow, LastCol))

rng.Name = "tbl_P"

 

[Shiseiji]

Thank You!! The second example is exactly what I was looking for. And I fooled around enough to see the difference though I had no idea how to write it.


I tried to make the subject descriptive enough to help others in the future, I'm sure someone else will find this helpful!

Ron

 

[Shiseiji]

Complete module. I use the prefix of "cm" to designate a common module that can be easily reused when copied to other workbooks.

Sub cm_LastRowLastColRng()
    'Answer provided by Norie
    '4-25-2017
    'https://www.mrexcel.com/forum/excel-questions/1002265-simple-select-last-row-last-column-name-range.html#post4810515
    ' Assuming no blank rows, columns, or formatting find the last row and the last column and name the range.
    ' Name is: LastRowLastColRng
    Dim thiswksht    As Worksheet
    Dim LastRow   As Long
    Dim LastCol   As Integer
    Dim rng       As Range
    Set thiswksht = ActiveSheet
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    Set rng = Range("A1", Cells(LastRow, LastCol))
    rng.Name = "tbl_P"
End Sub

Ron