Excel VBA:获取最后一个单元格包含所选范围内的数据 [英] Excel VBA: Get Last Cell Containing Data within Selected Range
本文介绍了Excel VBA:获取最后一个单元格包含所选范围内的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何使用Excel VBA获取包含特定范围内数据的最后一个单元格,例如列A和B 范围(A:B)
?
解决方案
使用查找
- 可以立即在2D范围内找到最后(或第一个)单元格
- 测试
没有任何
标识一个空白范围 - 将适用于可能不连续的范围(即
SpecialCells
范围)
更改YourSheet
到表格的名称正在搜索
Sub Method2()
Dim ws As Worksheet
Dim rng1 As Range
设置ws = Sheets(YourSheet)
设置rng1 = ws.Columns(A:B)。Find(*,ws。[a1],xlValues,xlByRows,xlPrevious)
如果不是rng1是没有,然后
MsgBox最后一个单元格& rng1.Address(0,0)
Else
MsgBox ws.Name& 列A:B为空,vbCritical
End If
End Sub
How do I use Excel VBA to get the last cell that contains data within a specific range, such as in columns A and B Range("A:B")
?
解决方案
using Find
like below is useful as it
- can find the last (or first) cell in a 2D range immediately
- testing for
Nothing
identifies a blank range - will work on a range that may not be contiguous (ie a
SpecialCells
range)
change "YourSheet"
to the name of the sheet you are searching
Sub Method2()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("YourSheet")
Set rng1 = ws.Columns("A:B").Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then
MsgBox "last cell is " & rng1.Address(0, 0)
Else
MsgBox ws.Name & " columns A:B are empty", vbCritical
End If
End Sub
这篇关于Excel VBA:获取最后一个单元格包含所选范围内的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文