excel vba select last row and last column

Question

I am trying to select from A9 to the lastrow & lastcolumn.

I have this to select the last cell, but it doesn't select from A9 to Last it just selects the lastrow/lastcolumn. Also this is not ideal because if I have blanks in the future.

I have searched and could not find anything for selecting from a cell to the lastrow & lastcolumn

Sub FindLast()
Application.ScreenUpdating = False

Range("A9").End(xlToRight).End(xlDown).Select

Application.ScreenUpdating = True
End Sub

Search order in my file would be Column A & Row 8 if that helps at all.

Code Below is what I am using to work on active sheets

Sub SelectAll()
Application.ScreenUpdating = False

Dim lastRow As Long, lastCol As Long
Dim Rng As Range
Dim WholeRng As Range

With ActiveWorksheet
    Set Rng = Cells

    'last row
    lastRow = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

    'last column
    lastCol = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

    Set WholeRng = Range(Cells(9, "A"), Cells(lastRow, lastCol))
    WholeRng.Select
End With

Application.ScreenUpdating = True
End Sub

First, you probably don't want to actually `Select it, please see [How to Avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Also, where have you searched? What have you tried? This question has been asked many times (see "related" posts to the right). — BruceWayne, Apr 11 '17 at 18:07

Shai Rado · Answer 1 · 2017-04-11T18:19:25.067

The safest way is use the Find function:

Option Explicit  

Sub LastRow_Col_Find()

    ' Safest way to ensure you got the last row:
    Dim lastRow As Long, lastCol As Long
    Dim Rng As Range
    Dim WholeRng As Range

    With Worksheets("report")   
        Set Rng = .Cells
        ' Safest way to ensure you got the last row
        lastRow = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        'MsgBox lastRow ' <-- for DEBUG

        ' Safest way to ensure you got the last column            
        lastCol = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        'MsgBox lastColumn ' <-- for DEBUG

        ' set the Range for the entire UsedRange in "YourSheetName" sheet
        Set WholeRng = .Range(.Cells(9, "A"), .Cells(lastRow, lastCol))
        WholeRng.Select '<-- ONLY IF YOU MUST
    End With
End Sub

score 2 · Accepted Answer · answered Apr 11 '17 at 22:18

2

Or you could exploit UsedRange

Sub FindLast()
    With Activesheet
        .Range(.Range("A9"), .UsedRange.Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Select
    End With
End Sub

answered Apr 11 '17 at 22:18

user3598756

28,893
4
18
28

excel vba select last row and last column

2 Answers2

Linked