Return a range from A1 to the true last used cell

Question

I'd like to select all the rows and columns in a spreadsheet. The macro needs to be dynamic, as the number of columns and rows tend to vary each time the macro would be called. It also needs to be able to account for blank rows and columns.

This subroutine accomplishes part of the process:

Sub FindLastCell()
Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub

It finds and selects the very last cell in a spreadsheet. Now that I've found the very last cell in the spreadsheet, how do I select cell A1 to the LastCell as a range?

brettdj · Accepted Answer · 2016-07-15T05:56:03.153

You need to make these mods to the code

The sheet may be blank, so you should never use Select with Find as this will give an error if the Find returns nothing. Instead test that the range object Is Not Nothing
Find can search by row and by column. You need to determine both last row and column to determine the true last used cell
Once you have determined the true last cell use Range to set a range from the first cell (A1) to your cell determined with the two Find ranges

Pls see the code below

If the Find gets a value then it makes a range rng3 from A1 to the last used cell identified by the two Finds.

enter image description here

Sub GetRange()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column))
        MsgBox "Range is " & rng3.Address(0, 0)
        'if you need to actual select the range (which is rare in VBA)
        Application.Goto rng3 
    Else
        MsgBox "sheet is blank", vbCritical
    End If
End Sub

Okay, that is a pretty kewl kewl way to perform the range function. I look the use of a procedure and use of iteration over recursion if only for the difference. — Scott Conover, Jul 26 '12 at 20:50

Gaijinhunter · Answer 2 · 2011-11-27T09:26:35.940

11

Selecting A1 to the last cell used (including blanks in-between) in Column A is as simple as this:

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select

To select A1 to the last cell used in the entire sheet (regardless if it's used in column A or not):

Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select

edited Nov 27 '11 at 09:26

answered Nov 27 '11 at 09:21

Gaijinhunter

14,587
4
51
57

3

xlCellTypeLastCell will typically over estimate the last used cell position (from deleted data) unless you precede it with forcing a usedrange update, ie adding a line such as `ActiveSheet.UsedRange`. But I agree it is shorter than the Find I used which I adapted from a more specific need. cheers Dave. – brettdj Nov 27 '11 at 09:32
Thanks for adding the note about refreshing usedrange! That's an important thing I should've mentioned. – Gaijinhunter Nov 27 '11 at 11:19
What is wrong with my below selection? MyRange = Range(myfile.Sheets("Summary").Range("A5:A") & myfile.Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row) - 2) – Isu Jul 13 '17 at 23:13
@aevanko, i tested the second line of code using `xlCellTypeLastCell` and it only selects column A. – GMalc Apr 03 '18 at 03:17

score 2 · Answer 3 · answered Oct 09 '12 at 15:46

I figured out how to modify the code to select a range beginning at a specified cell, and ending at the last cell. On line 8, change the a1 to the cell you wish to start on. In my case, I chose j28.

 Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column))

full code:

    Sub GetRange()
        Dim rng1 As Range
        Dim rng2 As Range
        Dim rng3 As Range
        Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious)
        Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious)
        If Not rng1 Is Nothing Then
            Set rng3 = Range([j28], Cells(rng1.Row, rng2.Column))
            MsgBox "Range is " & rng3.Address(0, 0)
            'if you need to actual select the range (which is rare in VBA)
            Application.Goto rng3
        Else
            MsgBox "sheet is blank", vbCritical
        End If
    End Sub

score 0 · Answer 4 · answered Apr 03 '18 at 04:04

0

@brettdj pointed out the use of ActiveSheet.UsedRange to clear the current used range. I would use the simple code below.

ActiveSheet.UsedRange
ActiveSheet.UsedRange.Select

answered Apr 03 '18 at 04:04

GMalc

2,608
1
9
16

Return a range from A1 to the true last used cell

4 Answers4

Linked

Related