Click here to Skip to main content
15,850,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


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
Posted

1 solution

Try this:
VB
'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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900