Excel 2010 VBA 入门 037 获取最后一行数据的行数

目录

示例:

实现代码1

使用Find方法查找最后的单元格

Find方法按行查找和按列查找的区别

实现代码2

获取工作表总行数

实现代码3

单元格对象的End属性

End属性的局限性

其他获取最后数据行行号的方法

1.定位(SpeciaICells)

2.UsedRange

实现代码4

3. CurrentReion


示例:

    在VBA中,经常需要对原始数据进行统计。然而在大多数情况下,数据的总行数是未知的,因而会给编写通用性较高的代码造成一些麻烦。如图所示,该表为某公司某月的销售人员销售数据。现需要对该表进行统计,由于销售人员的人数每月不固定,如何利用VBA动态获取最后一行数据的行号(以C列为准),以使程序更具备通用性?

销售地区 销售人员 销售金额
广州 程建华 135000
深圳 李国敏 67200
深圳 袁志刚 79500
深圳 周汉林 225000
广州 骈永富 15400
广州 孙玉梅 2500
上海 陈亚菁 37400
上海 康小芸 12000
上海 刘晨 22500
广州 齐光 73500
广州 于健惠 235000
山东 王文群 136500
山东 曾国安 21000
广州 刘志峰 16000
广州 刘玉录 78000
广州 俞卫广 36000
广州 杨建军 170000
深圳 曲波 38000
深圳 林革壮 73500
深圳 李卫卿 45000
广州 申玲 132000
上海 孙正发 63000
上海 毛传阳 106500
广州 张元端 147000
广州 朱凌波 265000
山东 张宏 34500
山东 郦锡文 300000
广州 张占斌 32500
广州 曹阳 205000
广州 周书敬 22400
广州 姚胜 215000
深圳 郭建 19500
深圳 高波 114000
深圳 卢卫 120000
广州 赵秀池 660000


实现代码1

Option Explicit

Sub 获取数据的总行数1()
    Dim RowN As Integer
    Dim Rng As Range
    
    Set Rng = Range("C:C").Find("*", after:=Range("C1"), searchdirection:=xlPrevious)
    RowN = Rng.Row
    Debug.Print "最后一个单元格为:"; Rng.Address; ; "行号为:"; RowN
    
    '另一个方法
    Set Rng = Range("A:C").Find("*", after:=Range("A1"), searchorder:=xlByColumns, searchdirection:=xlPrevious)
    RowN = Rng.Row
    Debug.Print "最后一个单元格为:"; Rng.Address; ; "行号为:"; RowN
End Sub

使用Find方法查找最后的单元格

    利用Find方法可以查找非空单元格,只需填写查找内容为“*”即可。如要查找C列的最后一个非空单元格,因而将参数After设为Range(“C1”),而SearchDirection设为xIPrevious,表示按照向上的方向进行查找。Excel默认的方向为向下和向右,xlPrevious
表示按照向左或向上的方向查找。而Find方法查找是在指定的范围内循环的。若设置在C列范围内,起始查找单元格为C1,方向为xIPrevious,Find方法会接着从C列最后一个单元格(即C1048576)开始查找。因而找到最后一个非空单元格(即C36)。

Find方法按行查找和按列查找的区别

    Find方法中,SearchOrder参数可以设置为xIByRows(按行查找)或者xIByColumns(按列查找),当SearchDirection设置为xINext时,其查找顺序如图所示。

Excel 2010 VBA 入门 037 获取最后一行数据的行数

    当SearchDirection设为xIPrevious时,其方向正好与图所示的方向相反。本例中,在A至C列,从A1开始向xIPrevious的方向,按列查找非空的单元格,即也是从C列的最后一个单元格开始找C列的非空单元格。在本例中,由于各列数据的行数相同,因而按行或按列的方式查找均可。但在下图所示的情况下,若按行(xIByRows)查找,则会找到A37单元格,而非C36单元格。

Excel 2010 VBA 入门 037 获取最后一行数据的行数

实现代码2

'计算量超大
Sub 获取数据的总行数2()
    Dim RowN As Long '不可用integer类型
    For RowN = Rows.Count To 1 Step -1
        If Cells(RowN, "C").Value <> "" Then
              Debug.Print "最后一个单元格为:C"; RowN
              Exit For
        End If
    Next
End Sub

获取工作表总行数

    在不同版本的Excel中,工作表的总行数是不同的,在Excel 2003及之前的版本中,工作表的总行数为65536(即2的16次方),而自Excel 2007开始的版本,总行数扩大到了1048576(即2的20次方)。为了让程序更通用,一般以以下方式表示工作表的总行数。其中,Rows表示所有的行,Count属性表示数量,即所有行的数量,也就是工作表的总行数。

实现代码3

Sub 获取数据的总行数3()
    
    Dim RowN As Long
    Dim Rng As Range
    If Cells(Rows.Count, "C").Value = "" Then
        Set Rng = Cells(Rows.Count, "C").End(xlUp) '上移一格
    Else
        Set Rng = Cells(Rows.Count, "C")
    End If
    RowN = Rng.Row
    Debug.Print "最后一个单元格为:"; Rng.Address; ; "行号为:"; RowN
End Sub

单元格对象的End属性

    单元格对象的End属性相当于寻找以某个单元格为起点按组合键【Ctrl+方向键】所定位到的单元格。当起始单元格值不为空时,将找到该方向上最后一个非空单元格,当起始单元格值为空时,将找到第一个非空单元格。使用End属性将返回一个单元格对象(Range),其语法为:

Rng.End(Direction)

    其中,Rng为单元格对象,表示起始单元格。Direction为方向参数,可以为表中的任何一个常量。

                               表 End属性Direction参数

    常  量

    说  明

xlDown

-4121

向下

xlToLeft

-4159

向左

xlToRight

-4161

向右

xlUp

-4162

向上

    本例中,程序首先判断C列最后一个单元格是否为空,若为空,则向上查找第一个非空单元格,反之,则C列最后一个有数据的单元格为该单元格。

End属性的局限性

    由于Excel的数据量通常不可能填满最后一个单元格,因而在绝大多数情况下可以方便地使用以下表达式表示某列最后一个非空单元格的行号。

Cells(Rows.Count,列号).End(xlUp).Row

    但是End属性返回的只能是可见单元格,一旦最后一行数据被隐藏了,则End属性将不会得到正确的结果,如图所示。因而在使用End属性时需要格外小心。
Excel 2010 VBA 入门 037 获取最后一行数据的行数

 

其他获取最后数据行行号的方法

    本例展示了3种获取最后数据行行号的方法,每个方法各有各的优点。除此之外,还有以下几种方法可以获取最后数据行的行号。

1.定位(SpeciaICells)

使用SpeciaICells方法,查找最后一个单元格,语句如下:

Range ("C:C").SpecialCells( xlCellTypeLastCell).Row

    但Excel判断最后一个单元格的方法并不以数据为准,在最后的数据行之后,只要设置过单元格格式或者曾经使用过的单元格,Excel都将断定其为最后的单元格。

2.UsedRange

  UsedRange是工作表对象(Worksheet)的一个属性,表示用户使用过的单元格区域。可以使用以下方法获取最后一个数据行的行号:

实现代码4

Sub 获取数据的总行数4()
    
    Debug.Print "最后一个单元格为:C"; ActiveSheet.UsedRange.Rows.Count

End Sub

    与SpeciaICells一样,只要是用户设置过格式或者写入任何内容的都将断定其为使用过的单元格。

3. CurrentReion

    CurrentReion属性是单元格对象的一个属性,表示该单元格所在的连续的数据区域。可以使用以下表达式获取最后数据行的行号:

Range("A1").CurrentRegion.Rows.Count

    该方法同样有局限,因为CurrentReion始终返回一个矩形的单元格区域。只要有一个单元格数据接壤,该区域将会扩大到包围接壤的数据单元格。如图所示,单元格A1的CurrentReion为A1:D37,因而上述表达式求得的行号为37。
Excel 2010 VBA 入门 037 获取最后一行数据的行数

上一篇:C#使用OFFICE库的参考开发文档


下一篇:VBA Type结构体可极大地提高程序的运行速度