none
Determine the last row RRS feed

  • Question

  • Hi,
    I am writing a code where the user can give up a range.
    The function is looking of the cell is filled in with a text and returns the row number of the last filled in cell.
    When I give up a range, Excel returns sometimes the number 7 as row while it is not the last filled in row...

    Can someone help me out with this?

    Function LastValueInColumn(range As range) As Integer
    For i = range.Rows.Count To 1 Step -1
    
    If Cells(i, 1).Value <> 0 Then
    LastValueInColumn = i
    Exit For
    End If
    
    Next i
    End Function

    Thursday, December 5, 2013 9:42 AM

Answers

  • If range does not start in row 1, but for example in row 5, or if range does not include column A, the result may not be what you want/expect. Try this version:

    Function LastValueInColumn(rng As Range) As Long
        Dim i As Long
        For i = rng.Rows.Count To 1 Step -1
            If rng.Cells(i, 1).Value <> 0 Then
                LastValueInColumn = rng.Cells(i, 1).Row
                Exit For
            End If
        Next i
    End Function

    (I changed range to rng since Range is a member of Excel's VBA object model).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by YannickB90 Thursday, December 5, 2013 2:16 PM
    Thursday, December 5, 2013 1:19 PM

All replies

  • If range does not start in row 1, but for example in row 5, or if range does not include column A, the result may not be what you want/expect. Try this version:

    Function LastValueInColumn(rng As Range) As Long
        Dim i As Long
        For i = rng.Rows.Count To 1 Step -1
            If rng.Cells(i, 1).Value <> 0 Then
                LastValueInColumn = rng.Cells(i, 1).Row
                Exit For
            End If
        Next i
    End Function

    (I changed range to rng since Range is a member of Excel's VBA object model).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by YannickB90 Thursday, December 5, 2013 2:16 PM
    Thursday, December 5, 2013 1:19 PM
  • Thanks, it works!
    Thursday, December 5, 2013 2:16 PM