VBA Excel 2007 - Cells.Find
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/8783694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel 2007 - Cells.Find
提问by HL8
Can someone please explain how to read the below code to find last column and row with data?
有人可以解释一下如何阅读下面的代码来找到最后一列和最后一行的数据吗?
Last_Column = Sheets("Combined").Cells.Find("", [a1], , , xlByColumns, xlPrevious).Column
Last_Row = Sheets("Combined").Cells.Find("", [a1], , , xlByRows, xlPrevious).Row
Thank you
谢谢
采纳答案by brettdj
To find the intersectof the last used column and row you should adapt your code to
要找到最后使用的列和行的交集,您应该将代码调整为
- Search for
"*"
to match any wildcard not for""
which is for blank cells (NB: with this change your code above will work in a limited sense (see 2-3 below) if you run it from thecombined
sheet - It is more robust not to assume the sheet has data, ie set ranges and then test they are
Not Nothing
When using a starting cell ie
'[a1]
you should specify the sheet name as well to esnure the code works if you are running it from different sheets, iews.[a1]
belowSub FindLast() Dim ws As Worksheet Dim rng1 As Range Dim rng2 As Range Set ws = Sheets("combined") Set rng1 = ws.Cells.Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious) Set rng2 = ws.Cells.Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then MsgBox "Last cell is " & Cells(rng1.Row, rng2.Column).Address(0, 0) Else MsgBox "No cells found" End If End Sub
- 搜索
"*"
以匹配不用于""
空白单元格的任何通配符(注意:如果您从工作combined
表运行它,您上面的代码将在有限的意义上起作用(请参阅下面的 2-3) - 不假设工作表有数据更可靠,即设置范围然后测试它们是
Not Nothing
使用起始单元格时,即
'[a1]
您还应该指定工作表名称以确保代码在从不同工作表运行时有效,即ws.[a1]
下面Sub FindLast() Dim ws As Worksheet Dim rng1 As Range Dim rng2 As Range Set ws = Sheets("combined") Set rng1 = ws.Cells.Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious) Set rng2 = ws.Cells.Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious) If Not rng1 Is Nothing Then MsgBox "Last cell is " & Cells(rng1.Row, rng2.Column).Address(0, 0) Else MsgBox "No cells found" End If End Sub