VBA小技巧：确定工作表数据区域

excelperfect

在使用VBA编写程序时，有几种常用方法可以在工作表中查找包含已有数据的区域，但这些方法都多少存在一些局限。

• Activesheet.Cells(1).CurrentRegion

如果已有数据区域中存在空行或空列，将无法获取正确的区域。

 

• Activesheet.Cells(Activesheet.Rows.Count,1).End(xlUp).Row

如果某个单元格中的数据位于更大编号的行但位于不同的列中，则得到的结果不正确。

 

• Activesheet.UsedRange

不是动态的。

 

• 仅使用按行或列查找（Find方法）可能会错过更大列（如果按行搜索）或更大行（如果按列搜索）中的异常值单元格。

 

下面的代码是“万无一失”的，它返回位于最大行和最大列（如果是全新的工作表，则为 A1）相交叉处的单元格。在复制、遍历或清除数据时，使用此函数将确保不会遗漏任何内容。

'查找工作表中最后使用的单元格

Function LastUsedCell(wksToUse As Worksheet) As Range

    Dim dblRow As Double

    Dim dblCol As Double

    Dim rngFound As Range

   

    Set LastUsedCell = wksToUse.Cells(1, 1)

    On Error GoTo Err_Exit

   

    Set rngFound = wksToUse.Cells.Find(What:='*', _

       LookIn:=xlFormulas, _

       LookAt:=xlPart, _

        SearchOrder:=xlByRows,_

       SearchDirection:=xlPrevious, _

       MatchCase:=False)

   

    If (Not(rngFound Is Nothing)) Then

       dblRow = rngFound.Row

        Set rngFound = wksToUse.Cells.Find(What:='*', _

           LookIn:=xlFormulas, _

            LookAt:=xlPart, _

           SearchOrder:=xlByColumns, _

           SearchDirection:=xlPrevious, _

           MatchCase:=False)

       dblCol = rngFound.Column

        Set LastUsedCell = wksToUse.Cells(dblRow, dblCol)

    End If

 

Housekeeping:

    Set rngFound = Nothing

    Exit Function

Err_Exit:

    Err.Clear

    Resume Housekeeping

End Function

 

在使用这个函数时，如果仅需要最后一行或最后一列，则可直接使用代码：

dblLastRow = LastUsedCell(Activesheet).Row

 

如果设置数据单元格区域，使用代码：

With Activesheet

    Set rngToUse = .Range(.Cells(1,1), LastUsedCell(Activesheet))

End With

 

如果行列信息都需要，则使用代码：

Set rngLastCell = LastUsedCell(Activesheet)

For dblRow = 1 to rngLastCell.Row

    For dblCol= 1 to rngLastCell.Column

 

这个函数适用于任何情况，即使对于空工作表也是如此，因为默认值是相关工作表的单元格 A1（决不为空）。这样做的原因是需要一些可靠的东西，不管数据的形状如何，不管Excel的各种变化，也不管工作表是否受到保护，都可以使用。

 

注：本程序来源于mrexcel.com，供学习参考。