UsedRange属性是Worksheet对象的一个有用的属性,可以返回工作表中已使用的单元格区域。实际上,根据UsedRange的意思,我们就可以明白,该属性代表工作表中已使用的区域,不仅包括可以看到内容的单元格,而且不包括应用了格式、添加批注或其他一些修改的单元格。我们可以使用UsedRange属性来操控Excel工作表数据。
由于UsedRange属性返回一个Range对象,因此Range对象可用的所有属性和方法也可用于UsedRange,这包括列/行计数、选择、清除、格式化和区域导航。此外,可以将其设置为命名对象以方便引用,如下所示:
Dim rng As Range
Set rng =Worksheets("MySheet").UsedRange
其中,“MySheet”是想要操作的工作表的名称。
设置命名对象(例如rng)后,在输入代码时就可以利用VBA的智能提示工具了。
使用UsedRange属性,可以方便地找到工作表中已使用的第一行、第一列、最后一行和最后一列,统计已使用区域的行列数以用于循环处理,等等。
应用1:选择工作表中已使用的区域
下面的代码选择当前工作表中已使用的区域:
Sub SelectUsedRange()
ActiveSheet.UsedRange.Select
End Sub
运行代码后的效果如下图1所示。
图1
从上图1中可以看到,VBA尝试使用电子表格上的数据来计算第一个单元格和最后一个单元格,并选择该区域范围内的所有内容。注意,在图1中,使用UsedRange属性时,还包括已使用区域范围内的任何空单元格。
正如刚刚所演示的那样,UsedRange属性总是产生一个矩形区域,从最左上角单元格开始,直到最下面的行和最右边的列。即使它们定义了UsedRange属性返回的区域的边界,左上角和右下角单元格也可能实际上不包含任何值。
例如下图2所示,UsedRange属性返回单元格区域C1:F25,即便该区域四个角上的单元格中都没有数据或格式化,以及数据中间还有一个空行。
图2
应用2:获取工作表已使用单元格区域地址
下面的代码在立即窗口中打印工作表已使用区域的地址:
Dim rng As Range
Set rng =Worksheets("MySheet").UsedRange
Debug.Print rng.Address
对于上图2所示的工作表,返回字符串C1:F25,该区域的第一行(是工作表第1行),第一列(是工作表列C),最后一行(是工作表第25行),最后一列(是工作表列F)。
应用3:找到工作表已使用区域的第一行和第一列
使用UsedRange属性,结合Range对象的Row属性和Column属性,很容易找到工作表已使用区域的第一行和第一列:
Dim rng As Range
Set rng =Worksheets("MySheet").UsedRange
Debug.Print rng.Row
Debug.Print rng.Column
对于上图2所示的工作表,返回代表工作表已使用区域第一行和第一列的数字,即1和3,对应于单元格C1。
应用3:统计行数和列数
可以使用Count属性来统计工作表已使用区域的行数和列数:
Dim rng As Range
Set rng =Worksheets("MySheet").UsedRange
Debug.Print rng.Rows.Count
Debug.Print rng.Columns.Count
对于上图2所示的工作表,返回25行4列。
应用4:找到工作表已使用区域的最后一行和最后一列
使用下面的代码,获取工作表已使用区域的最后一行和最后一列:
Dim rng As Range
Dim firstRow As Long, lastRow As Long
Dim firstCol As Long, lastCol As Long
Dim numRows As Long, numCols As Long
Set rng =Worksheets("MySheet").UsedRange
firstRow =rng.Row
firstCol =rng.Column
numRows =rng.Rows.Count
numCols =rng.Columns.Count
lastRow =firstRow + numRows - 1
lastCol =firstCol + numCols - 1
注意,在计算最后一行和最后一列时,要减去1,以避免重复计算第一行和第一列。
其实还有更简单的方式,如下:
Dim rng As Range
Dim lastRow As Long, lastCol As Long
Set rng =Worksheets("MySheet").UsedRange
lastRow =rng.Rows(rng.Rows.Count).Row
lastCol =rng.Columns(rng.Columns.Count).Column
应用5:查找工作表最后一个单元格
找到工作表已使用区域最后一行和最后一列后,就可以知道其最后一个单元格了。接上:
Cells(lastRow,lastCol)
即为工作表最后一个单元格。
应用6:用于循环计数
假设工作表中仅在列A中包含数字数据,可以使用下面的程序将总数存储在列B(第2列)中:
Sub EnterTotal()
Dim firstRow As Long
Dim lastRow As Long
Dim lRow As Long
Dim rng As Range
Set rng = ActiveSheet.UsedRange
firstRow = rng.Row
lastRow = rng.Rows(rng.Rows.Count).Row
For lRow = firstRow To lastRow
If lRow = firstRow Then
Cells(lRow, 2) = Cells(lRow, 1)
Else
Cells(lRow, 2) = Cells(lRow, 1) +Cells(lRow - 1, 2)
End If
Next lRow
End Sub
需要两个嵌套循环才能遍历已使用区域内的行和列:
Sub LoopThroughUsedRange()
Dim firstRow As Long, lastRow As Long
Dim firstCol As Long, lastCol As Long
Dim lRow As Long, lCol As Long
Dimrng As Range
Set rng = ActiveSheet.UsedRange
firstRow = rng.Row
firstCol = rng.Column
lastRow = rng.Rows(rng.Rows.Count).Row
lastCol =rng.Columns(rng.Columns.Count).Column
For lCol = firstCol To lastCol
For lRow = firstRow To lastRow
Debug.Print Cells(lRow,lCol).Address & " = " & Cells(lRow, lCol)
Next lRow
Next lCol
End Sub
技巧:如果想排除已使用区域中的空单元格,可以结合使用IsEmpty函数。
一旦理解了如何导航UsedRange,使用VBA应用相关属性就会轻而易举:可以一次执行诸如将整个区域更改为粗体之类的操作。注意,这样的操作对区域中的空单元格也有效。
应用7:设置单元格字体
下面的代码将工作表已使用区域内容加粗:
Dim rng As Range
Set rng =Worksheets("MySheet").UsedRange
rng.Font.Bold= True
下面的代码将工作表已使用区域中的第3列加粗:
Dim rng As Range
Set rng =Worksheets("MySheet").UsedRange
rng.Columns(3).Font.Bold= True
这样的代码更健壮,不会因为在已使用区域外插入/删除行而变化,也不因将该区域移动而变化。
也可以稍作修改,对单元格区域设置填充颜色、数字格式等操作。
应用8:清除单元格内容
下面的代码将清除工作表中已使用区域内容:
Dim rng AsRange
Set rng =Worksheets("MySheet").UsedRange
rng.Clear
最后再提示一点,如果在数据区域外,还有没有输入数据但应用了格式的单元格,此时的工作表已使用区域将扩大至该单元格所在的行列范围,此时需要一些额外的处理。