excel - xlup 正在查找文本，但查找“”的公式，如何转换为查找非“”/空白单元格？

Question

Counter = Cells(Rows.Count, 4).End(xlUp).Row - 1

所以我用它来查找被占用的单元格，但它正在查找具有以“”结尾的 IF 公式的单元格。

我可以将其转换为查找可见文本吗？

先感谢您

标签： excelvba

Answer 1

最后一行排除包含计算为“”的公式的单元格

使用Find方法

• 这是一个链接，可以查看 Microsoft 对该Find方法的说明。
• 这是Siddharth Rout 的关于使用该方法查找最后一个单元格（行）的传奇帖子的链接。Find
• 请注意，这Counter是“指向”最后一行上方的一行。

编码

Sub testGetLastRowInColumn()

    Dim LastRow As Long
    Dim Counter As Long

' Simple, for the ActiveSheet:
    LastRow = getLastRowInColumn("A") ' 1 or "A", both are allowed.
    Debug.Print LastRow

' Proper, for a certain sheet:
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    LastRow = getLastRowInColumn("A", ws)
    Debug.Print LastRow

' In your case
    LastRow = getLastRowInColumn(4)
    If LastRow > 0 Then
        Counter = LastRow - 1
    End If
    Debug.Print LastRow, Counter
    
' In your case without the function:
    LastRow = Columns(4).Find(What:="*", _
                              LookIn:=xlValues, _
                              SearchDirection:=xlPrevious).Row
    If LastRow > 0 Then
        Counter = LastRow - 1
    End If
    Debug.Print LastRow, Counter

' Or simplified:
    LastRow = Columns(4).Find("*", , xlValues, , , xlPrevious).Row
    If LastRow > 0 Then
        Counter = LastRow - 1
    End If
    Debug.Print LastRow, Counter

' Or just (if you're sure that there is data in column 4):
    Counter = Columns(4).Find("*", , xlValues, , , xlPrevious).Row - 1
    Debug.Print LastRow, Counter

End Sub

Function getLastRowInColumn(ColumnIndex As Variant, _
                            Optional Sheet As Worksheet = Nothing, _
                            Optional includeEmpties As Boolean = False)
    
    If Sheet Is Nothing Then
        Set Sheet = ActiveSheet
    End If
    
    Dim FormVal As XlFindLookIn
    If includeEmpties Then
        FormVal = xlFormulas
    Else
        FormVal = xlValues
    End If
    
    Dim rng As Range
    Set rng = Sheet.Columns(ColumnIndex).Find(What:="*", _
                                              LookIn:=FormVal, _
                                              SearchDirection:=xlPrevious)
    If Not rng Is Nothing Then
        getLastRowInColumn = rng.Row
    Else
        getLastRowInColumn = 0
    End If
    
End Function