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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 14:52:35  来源:igfitidea点击:

VBA Excel 2007 - Cells.Find

excelvba

提问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

要找到最后使用的列和行的交集,您应该将代码调整为

  1. 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 the combinedsheet
  2. It is more robust not to assume the sheet has data, ie set ranges and then test they are Not Nothing
  3. 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, ie ws.[a1]below

    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
    
  1. 搜索"*"以匹配不用于""空白单元格的任何通配符(注意:如果您从工作combined表运行它,您上面的代码将在有限的意义上起作用(请参阅下面的 2-3)
  2. 不假设工作表有数据更可靠,即设置范围然后测试它们是 Not Nothing
  3. 使用起始单元格时,即'[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