vba 确定列中的最后一个非值（可能有公式）行

Determine last non-value (may have a formula) row in column

提问by Jim

I have a column that has a formula in each row field. The formula propagates data from another Excel spreasheet. If there is nothing in the row field, though, the row remains blank.

我有一列，每行字段都有一个公式。该公式从另一个 Excel 电子表格传播数据。但是，如果行字段中没有任何内容，该行将保持空白。

I have found many examples to get the last row in a column. They fail, because they detect the formula as the row having something in it.

我找到了许多示例来获取列中的最后一行。他们失败了，因为他们将公式检测为包含某些内容的行。

How can I get the last row in a column that ignores the formula and only attempts to detect values?

如何获取忽略公式并仅尝试检测值的列中的最后一行？

I tried two methods for searching for the last row in a column:

我尝试了两种方法来搜索列中的最后一行：

Function lastRowA(rngInput As Range) As Variant
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Rows(1).EntireRow
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            lastRowA = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

and

和

function lastRow(column as string, optional plusOne as boolean)
    If (plusOne = False) then
        plusOne=False
    End If

    if (plusOne = False) Then
        lastRow = Replace(Range(column & "65536").End(xlUp).Address, "$", "")
    Else
        lastRow = Range(column & "65536").End(xlUp).Address
        lastRow = Cells(lastRow)
        ' Replace(, "$", "")
    End If
End Function

回答by Doug Glancy

I've never done anything quite like this, but it seems to work correctly and quicky for fairly large areas. Even though you said the column is all formulas, this accounts for a mix of values and formulas, thus the outer loop stepping backwards through the Areas:

我从来没有做过这样的事情，但是对于相当大的区域，它似乎可以正确和快速地工作。即使您说该列都是公式，这也说明了值和公式的混合，因此外部循环在区域中向后步进：

Function GetLastFormulaBlank(rngInput As Excel.Range) As Excel.Range

Dim rngFormulas As Excel.Range
Dim rngArea As Excel.Range
Dim CellCounter As Long
Dim AreaCounter As Long
Dim varAreaCells As Variant
Dim rngLastFormulaBlank As Excel.Range

Set rngFormulas = rngInput.SpecialCells(xlCellTypeFormulas)
For AreaCounter = rngFormulas.Areas.Count To 1 Step -1
    Set rngArea = rngFormulas.Areas(AreaCounter)
    varAreaCells = rngArea.Value2
    If IsArray(varAreaCells) Then
        For CellCounter = UBound(varAreaCells) To LBound(varAreaCells) Step -1
            If varAreaCells(CellCounter, 1) = "" Then
                Set rngLastFormulaBlank = rngArea.Cells(CellCounter)
                Exit For
            End If
        Next CellCounter
    Else
        If varAreaCells = "" Then
            Set rngLastFormulaBlank = rngArea.Cells(1)
        End If
    End If
    If Not rngLastFormulaBlank Is Nothing Then
        Exit For
    End If
Next AreaCounter

Set GetLastFormulaBlank = rngLastFormulaBlank
End Function

You'd call it like this:

你会这样称呼它：

Sub test()
Dim rngLastFormulaBlank As Excel.Range

Set rngLastFormulaBlank = GetLastFormulaBlank(ActiveSheet.Range("A:A"))
If Not rngLastFormulaBlank Is Nothing Then
    MsgBox rngLastFormulaBlank.Address
Else
    MsgBox "no formulas with blanks in range"
End If
End Sub

回答by aevanko

Here's a simple way to find the last cell in a column that does not contain a formula. It will be 0 if there is no cell without a formula.

这是在不包含公式的列中查找最后一个单元格的简单方法。如果没有没有公式的单元格，它将为 0。

Sub Test()

Dim i As Long, tempLast As Long, lastRow As Long
tempLast = Range("A" & Rows.Count).End(xlUp).Row

For i = tempLast To 1 Step -1
    If Len(Cells(i, 1)) <> 0 Then
        If Not Cells(i, 1).HasFormula Then
            lastRow = i
            Exit For
        End If
    End If
Next

MsgBox lastRow
End Sub

Note that you should use "rows.count" and not 65536 as that is no longer the last row in the newer versions of Excel. Rows.count work no matter the version, or user settings. Usedrange should also be avoided since there is a weird bug where you need to refresh the usedrange or you'll get erroneous results.

请注意，您应该使用“rows.count”而不是 65536，因为它不再是较新版本 Excel 中的最后一行。无论版本或用户设置如何，Rows.count 都有效。也应该避免使用 Usedrange，因为有一个奇怪的错误，您需要刷新 usedrange 否则您会得到错误的结果。

回答by Charles Williams

If you want to find the last row that contains a non-blank value (either produced by a formula or by entering a constant) try this

如果要查找包含非空值（由公式生成或通过输入常量生成）的最后一行，请尝试此操作

Sub FindLastValue()
Dim jLastRow As Long
jLastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub

回答by Mohsin Shahed Rana

Please follow below code.

请按照下面的代码。

Sub Test()
    Dim i As Long, tempLast As Long, lastRow As Long
    tempLast = Range("C" & Rows.Count).End(xlUp).Row

    For i = 1 To tempLast
          If Cells(i, 1).HasFormula Or Not Cells(i, 1).HasFormula Then
             If Len(Cells(i, 1).Value) < 1 Then
                lastRow = i
                Exit For
            End If
        End If
    Next

    MsgBox lastRow
End Sub