excel按条件选择工作表_在Excel工作表中选择“实际使用范围”

最新推荐文章于 2022-07-15 09:20:24 发布

culiao2169

最新推荐文章于 2022-07-15 09:20:24 发布

阅读量775

点赞数

文章标签： excel vba manjaro 开发工具比特币

原文链接：https://contexturesblog.com/archives/2012/03/01/select-actual-used-range-in-excel-sheet/

版权

excel按条件选择工作表

It's easy to select the current range in Excel – just press Ctrl + A. That shortcut selects all the cells in the block that surround the active cell. The selection stops at the first blank row and blank column. But how can you select actual used range in Excel sheet?

在Excel中选择当前范围很容易-只需按Ctrl +A。该快捷方式将选择活动单元周围的块中的所有单元。选择在第一空白行和空白列处停止。但是，如何在Excel工作表中选择实际使用的范围？

选择当前范围 (Select Current Range)

In this example, cell A1 was active, and range A1:A2 was selected with the keyboard shortcut.

在此示例中，单元格A1处于活动状态，并且使用键盘快捷键选择了范围A1：A2。

选择所有单元格 (Select All Cells)

If you use the Ctrl + A shortcut while an isolated or empty cell is selected, then the entire worksheet is selected. In the screen shot below, cell E2 was active when the shortcut was used. The entire sheet was selected.

如果在选择一个孤立的或空的单元格时使用Ctrl + A快捷方式，则将选择整个工作表。在下面的屏幕快照中，使用快捷方式时，单元格E2处于活动状态。整个表被选中。

If the Ctrl + A shortcut selects a range, you can press Ctrl + A again, to select the entire sheet. In the screen shot below, cell A1 was active, and by pressing Ctrl + A twice, the entire worksheet is selected.

如果Ctrl + A快捷键选择了一个范围，则可以再次按Ctrl + A来选择整个工作表。在下面的屏幕快照中，单元格A1是活动的，并且通过按Ctrl + A两次，选择了整个工作表。

选择仅填充单元格 (Select Filled Cells Only)

To select only the filled cells on a worksheet, you can use the Find dialog box.

要仅选择工作表上的填充单元格，可以使用“查找”对话框。

On the Excel Ribbon's Home tab, click Find & Select, then click Find (or use the keyboard shortcut -- Ctrl+F)
在Excel功能区的“主页”选项卡上，单击“查找和选择”，然后单击“查找”(或使用键盘快捷键-Ctrl + F)
Type an asterisk (*) in the "Find what" field
在“查找内容”字段中输入星号(*)
Click the "Find All" button
点击“查找全部”按钮
Click Close
点击关闭

Only the filled cells on the worksheet are selected.

仅选择工作表上的已填充单元格。

选择使用范围 (Select the Used Range)

To select all the cells in the used range on a worksheet, you can use the following shortcut sequence:

若要选择工作表上已使用范围内的所有单元格，可以使用以下快捷方式序列：

Press Ctrl + Home, to select cell A1
按Ctrl + Home，选择单元格A1
Press Ctrl + Shift + End, to select all cells from A1 to the last used cell.
按Ctrl + Shift +结束，以选择从A1到最后使用的单元格的所有单元格。

选择实际使用范围 (Select the Actual Used Range)

As you can see in the screen shot above, there is nothing visible in cell D8, but it is included in the selected used range. Perhaps there was a value in that cell, and it was deleted, or the cell is formatted.

从上面的屏幕快照中可以看到，单元格D8中没有可见的内容，但它包含在所选的使用范围内。可能在该单元格中有一个值，该值已被删除，或者该单元格已格式化。

If you want to select only the cells in the actual used range, you can use this macro instead. The macro was written by Rick Rothstein, and looks for the last cell with a value, and ends the selection there.

如果只想选择实际使用范围内的单元格，则可以改用此宏。该宏由Rick Rothstein编写，查找具有值的最后一个单元格，然后在此处结束选择。

Note: This code ignores cells with formulas that are displaying the empty string. If you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.

注意：此代码将忽略具有显示空字符串的公式的单元格。如果需要标识可能显示空字符串的公式单元格，则将xlValues参数更改为xlFormulas。

Sub PickedActualUsedRange()
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

After running the PickedActualUsedRange macro, the selection ends at cell D7, instead of D8.

运行PickedActualUsedRange宏后，选择在单元格D7而不是D8处结束。

从第一个具有数据的单元格中选择 (Select from First Cell With Data)

[Thanks to Jelle-Jeroen Lamkamp for his comment that the first used cell might not be A1. Here is Rick's code to deal with that situation.] Usually, selecting from cell A1 won't hurt anything, but if you really want the actual used range on the worksheet, then you would need to use a macro like this one...

[感谢Jelle-Jeroen Lamkamp的评论，即第一个使用的电池可能不是A1。这是里克(Rick)处理这种情况的代码。]通常，从单元格A1中进行选择不会有任何损害，但是，如果您确实想要工作表上的实际使用范围，则需要使用像这样的宏...

Sub SelectActualUsedRange()
  Dim FirstCell As Range, LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)
  Range(FirstCell, LastCell).Select
End Sub