VBA实战技巧01：在代码中引用动态调整单元格区域的5种方法

fanjy

发布于 2020-02-12 04:09:47

4.9K07

代码可运行

文章被收录于专栏：完美Excel完美Excel

运行总次数：7

代码可运行

在VBA代码中，经常要引用单元格数据区域并对其进行操作。然而，如果对数据区域采用“硬编码”地址，那么当该区域大小变化时，必须修改相应的引用该区域的代码。本文整理了可以动态引用数据区域的5种方法，供编写代码时参考。

方法1：使用UsedRange属性

工作表对象的UsedRange属性返回一个Range对象，代表工作表中已使用的单元格区域。代码：

Sub DynamicRange1()
   '刷新已使用区域
    ActiveSheet.UsedRange
   '选择已使用区域
    ActiveSheet.UsedRange.Select
End Sub

方法2：使用Ctrl+Shift+向右箭头键/向下箭头键

模拟使用快捷键Ctrl+Shift+右箭头，然后使用快捷键Ctrl+Shift+向下箭头来选择单元格区域。注意，如果第一行的最后一个单元格或者第一列的最后一个单元格为空，则本方法不会选择到正确的单元格区域。因此，本方法适用于数据区域的第一列在最后一行有值且第一行在最后一列有值的区域。

Sub DynamicRange2()
    Dim wks As Worksheet
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim FirstCell As Range
   
   '设置工作表和数据区域起始单元格
    Set wks = Worksheets("Sheet1")
    Set FirstCell = Range("C3")
   
    With wks
        '获取数据区域第一列中有数据的最后一行行号
        lngLastRow = .Cells(.Rows.Count,FirstCell.Column).End(xlUp).Row
        '获取数据区域第一行中有数据的最后一列表号
        lngLastColumn = .Cells(FirstCell.Row,.Columns.Count).End(xlToLeft).Column
        '选择数据区域
        .Range(FirstCell, Cells(lngLastRow,lngLastColumn)).Select
    End With
End Sub

方法3：SpecialCells方法——最后一个单元格

使用SpecialCells方法来查找工作表中包含数据的最后一个单元格。找到后，使用该单元格引用来确定最后的数据行和列。

Sub DynamicRange3()
    Dim wks As Worksheet
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim FirstCell As Range
   
   '设置工作表和起始单元格
    Set wks = Worksheets("Sheet1")
    Set FirstCell = Range("C3")
   
   '刷新已使用单元格区域
    Worksheets("Sheet1").UsedRange
   
   '找到最后一行和列
    With FirstCell.SpecialCells(xlCellTypeLastCell)
        lngLastRow = .Row
        lngLastColumn = .Column
    End With
   
   '选择单元格区域
    wks.Range(FirstCell, wks.Cells(lngLastRow,lngLastColumn)).Select
End Sub

方法4：使用CurrentRegion属性

单元格对象的CurrentRegion属性返回当前单元格所在的数据区域，该区域上下左右都是空行。

Sub DynamicRange4()
    Dim wks As Worksheet
    Dim FirstCell As Range
   
   '设置工作表和起始单元格
    Set wks = Worksheets("Sheet1")
    Set FirstCell = Range("C3")
   
   '选择单元格区域
    FirstCell.CurrentRegion.Select
End Sub

方法5：固定列

很多情况下，工作表数据区域的列是固定的，只是随着数据的添加，行数发生变化。此时，可以使用下面的代码：

Sub DynamicRange5()
    Dim wks As Worksheet
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim FirstCell As Range
   
   '设置工作表和起始单元格
    Set wks = Worksheets("Sheet1")
    Set FirstCell = Range("C3")
   
   '刷新已使用单元格区域
    Worksheets("Sheet1").UsedRange
   
   '查找最后一行
    lngLastRow = wks.Cells.Find("*",_
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
       
   '选择单元格区域
    wks.Range("C3:E" &lngLastRow).Select
End Sub