# Determine the last row

• ### 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

• 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 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 Thursday, December 5, 2013 2:16 PM
Thursday, December 5, 2013 1:19 PM
• Thanks, it works!
Thursday, December 5, 2013 2:16 PM