函数查找最后一行，但不会忽略写入的列中的第一行 [英] Function finds the last row, but not ignoring first row in column as written

问题描述

您好：



我写了以下函数来查找任何列的最后一行：

Hello:

I wrote the following function to find the last row of any column:

Public Function GetLastRow(ByVal rngToCheck As Excel.Range) As Long

    Dim rngLast As Excel.Range

    rngLast = rngToCheck.Find(What:="*", SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=Excel.XlSearchDirection.xlPrevious)

    If rngLast Is Nothing Then
        GetLastRow = rngToCheck.Rows.Count

    Else

        GetLastRow = rngLast.Rows.Count

    End If

End Function

现在，我想找到F列的最后一行，但是从F2开始。它必须忽略行F1，因为这是标题。但是，它不是，我得到一个错误，它无法从String转换为Double，这是预期的，因为F1是一个字符串（列标题），其他一切都是数据。我怎么能改变这个？



以下是我的其余代码：

Now, I want to find the last row on column F, but starting on F2. It has to ignore row F1 because that is the heading. However, it is not and I get an error that it cannot convert from String to Double, which is expected because F1 is a string (Column Title) and everything else is data. How can I change this?

Here is the rest of my code:

<pre>Sub renameColumns()

        With xlWSPosition


            .Columns("E").Insert(Excel.XlDirection.xlDown)
            .Range("E1").Value = "Exemption"
            .Cells.EntireColumn.AutoFit()


            Dim colValue As Excel.Range
            Dim lngLr As Long
            lngLr = GetLastRow(.Cells)


            For Each colValue In .Range("F2:F" & lngLr) 'Change range as needed

                If colValue.Value > 0 Then (Error here because F1 is string not double)

                    'used offset instead of range,ie (E1:E)
                    .Range(colValue.Address).Offset(0, -1).Value = "N"

                Else

                    .Range(colValue.Address).Offset(0, -1).Value = "Y"


                End If

            Next

        End With

    End Sub

推荐答案

试试这个：

Try this:

'returns first empty row ;)
Function GetFirstEmptyRow(wsh AS Worksheet, Optional sColName As String = "A") AS Long
    Return wsh.Range(sColName & wsh.Rows.Count).End(xlUp).Row + 1
End Function

这篇关于函数查找最后一行，但不会忽略写入的列中的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！