定位单行或列中的最后一个非空单元格(VBA)
本篇将以下图为例进行介绍
代码1
Sub lastrng()
Dim rngup, rngdown, rngleft, rngright As Range
Set rngup = Range("C5").End(xlUp)
Set rngdown = Range("C5").End(xlDown)
Set rngleft = Range("C5").End(xlToLeft)
Set rngright = Range("C5").End(xlToRight)
MsgBox rngup.Address & Chr(10) & rngdown.Address & Chr(10) & rngleft.Address & Chr(10) & rngright.Address
End Sub
运行结果:
代码2
Sub lastrng()
Set rngup = Range("C:C").Find("*", Range("C:C").Cells(Range("C:C").Cells.Count), , xlWhole, xlByColumns, xlNext)
Set rngdown = Range("C:C").Find("*", Range("C:C").Cells(1, 1), , xlWhole, xlByColumns, xlPrevious)
Debug.Print rngup.Address
Debug.Print rngdown.Address
End Sub
运行结果:
$C$3
$C$9
代码1和代码2的运行结果要认真比较,通常定位单行或单列的最后一个单元格代码1是最常用的方法。Find方法主要用于在一个区域中查找特定的值,代码2是对Find方法的一个变形使用,但是,用此方法定位多行或多列的最后一个行单元格或列单元格却非常的方便。