Excel VBA select range at last row and column

Question

I'm trying to create a macro that selects the range of last row and last column.

E.g. I want to select 1, 2, 3, 4 from my spreadsheet and then delete the selection.

Data:

John  | 10 | 10 | 10
Smith | 5  | 5  | 5
Fred  | 8  | 8  | 8 
1     | 2  | 3  | 4

Here is my code, it only selects the the last row on the A column. (selects 1 and deletes it). I need it to select 1 to 4 and delete the whole row.

Range("A" & Rows.Count).End(xlUp).Select
Selection.Delete Shift:=xlUp

Answer 1

Is this what you are trying? I have commented the code so that you will not have any problem understanding it.

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long
    Dim rng As Range

    '~~> Set this to the relevant worksheet
    Set ws = [Sheet1]

    With ws
        '~~> Get the last row and last column
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        '~~> Set the range
        Set rng = .Range(.Cells(lRow, 1), .Cells(lRow, lCol))

        With rng
            Debug.Print .Address
            '
            '~~> What ever you want to do with the address
            '
        End With
    End With
End Sub

BTW I am assuming that LastRow is the same for all rows and same goes for the columns. If that is not the case then you will have use .Find to find the Last Row and the Last Column. You might want to see THIS