vba 返回从 A1 到真正上次使用的单元格的范围

Return a range from A1 to the true last used cell

提问by AME

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?

它查找并选择电子表格中的最后一个单元格。现在我已经找到了电子表格中的最后一个单元格，如何选择单元格 A1 到 LastCell 作为范围？

回答by brettdj

You need to make these mods to the code

您需要对代码进行这些修改

1. The sheet may be blank, so you should never use Selectwith Findas this will give an error if the Find returns nothing. Instead test that the range object Is Not Nothing
2. Findcan search by row and by column. You need to determine bothlast row and column to determine the true last used cell
3. Once you have determined the true last cell use Rangeto set a range from the first cell (A1) to your cell determined with the two Findranges

1. 该工作表可能是空白的，因此您不应使用Selectwith，Find因为如果 Find 不返回任何内容，则会出现错误。而是测试范围对象Is Not Nothing
2. Find可以按行和按列搜索。您需要确定这两个最后的行和列，以确定真正的上次使用的电池
3. 一旦确定了真正的最后一个单元格，请使用Range设置从第一个单元格 (A1) 到由两个Find范围确定的单元格的范围

Pls see the code below

请看下面的代码

If the Findgets a value then it makes a range rng3from A1 to the last used cell identified by the two Finds.

如果Find得到一个值，那么它会产生rng3从 A1 到由两个Finds标识的最后使用的单元格的范围。

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

回答by aevanko

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

选择 A1 到 A 列中使用的最后一个单元格（包括中间的空白）就像这样简单：

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):

要选择 A1 到整个工作表中使用的最后一个单元格（无论它是否在 A 列中使用）：

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

回答by Micah Stubbs

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.

我想出了如何修改代码以选择从指定单元格开始到最后一个单元格结束的范围。在第 8 行，将 a1 更改为您希望开始的单元格。就我而言，我选择了 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

回答by GMalc

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

@brettdj 指出ActiveSheet.UsedRange用于清除当前使用的范围。我会使用下面的简单代码。

ActiveSheet.UsedRange
ActiveSheet.UsedRange.Select