确定列中的最后一个非值(可能有一个公式)行 [英] Determine last non-value (may have a formula) row in column

问题描述

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

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.

我发现了很多获取列最后一行的示例.之所以失败，是因为他们将公式检测为其中包含某些内容的行.

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

和

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

推荐答案

如果要查找包含非空白值(由公式生成或通过输入常量生成的值)的最后一行，请尝试

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

这篇关于确定列中的最后一个非值(可能有一个公式)行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！