返回从 A1 到真正上次使用的单元格的范围 [英] Return a range from A1 to the true last used cell

问题描述

我想选择电子表格中的所有行和列.宏需要是动态的，因为每次调用宏时，列数和行数往往会有所不同.它还需要能够考虑空白行和列.

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

它查找并选择电子表格中的最后一个单元格.既然我已经找到了电子表格中的最后一个单元格，我该如何选择单元格 A1 到 LastCell 作为一个范围?

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?

推荐答案

你需要把这些 mods 变成代码

You need to make these mods to the code

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

1. 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
2. Find can search by row and by column. You need to determine both last row and column to determine the true last used cell
3. 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

请看下面的代码

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

If the Find gets a value then it makes a range rng3 from A1 to the last used cell identified by the two 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

这篇关于返回从 A1 到真正上次使用的单元格的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！