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

score 15 · Answer 1 · edited May 23 '17 at 12:13

15

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

edited May 23 '17 at 12:13

Community

1
1

answered Sep 23 '13 at 05:57

Siddharth Rout

147,039
17
206
250

I put `Selection.Delete Shift:=xlUp` inside the With rng block of code but it doesn't delete the selection – user2273278 Sep 23 '13 at 06:05
It won't ;) Try `.Delete Shift:=xlUp` After the debug statement. – Siddharth Rout Sep 23 '13 at 06:13
Can you post the exact code that you are using in the question above? I just tested it and it works. – Siddharth Rout Sep 23 '13 at 06:14
I copied the exact code into my Module but it only deletes the the A column – user2273278 Sep 24 '13 at 01:20
I also tried changing the Set range to `range(Cells(myRow, myCol), Cells(myRow, myCol))` – user2273278 Sep 24 '13 at 01:21
May I see your excel file. There is obviously something which you are doing wrong. As I mentioned, I have tested the above code. – Siddharth Rout Sep 24 '13 at 05:29

score 5 · Answer 2 · answered Jul 15 '14 at 20:13

5

The simplest modification (to the code in your question) is this:

    Range("A" & Rows.Count).End(xlUp).Select
    Selection.EntireRow.Delete

Which can be simplified to:

    Range("A" & Rows.Count).End(xlUp).EntireRow.Delete

answered Jul 15 '14 at 20:13

Chuck Trese

161
2
4

score 0 · Answer 3 · answered Apr 13 '20 at 00:09

0

Another simple way:

ActiveSheet.Rows(ActiveSheet.UsedRange.Rows.Count+1).Select    
Selection.EntireRow.Delete

or simpler:

ActiveSheet.Rows(ActiveSheet.UsedRange.Rows.Count+1).EntireRow.Delete

answered Apr 13 '20 at 00:09

Crowdpleasr

3,574
4
21
37

Excel VBA select range at last row and column

3 Answers3

Linked