Answered by:
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
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
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
