Excel VBA: Determine the last used row using a named range

Question

I want to determine the last used row inside a range, using a named range I created.

Here is how I am doing it.

With ActiveSheet
    Dim textboxValue As String, lastUsedRow As Long
    textboxValue = UserForm.TextBox1
    lastUsedRow = .Range(textboxValue ).Rows.Count
    Cells(lastUsedRow).Select
End With

This is what's in the worksheet so the last used row should be the one with the word "No."

But the selected last used row is a cell with nothing in it.

Possible duplicate of [Excel VBA Find last row in range](https://stackoverflow.com/questions/40650508/excel-vba-find-last-row-in-range) — danieltakeshi, Jun 22 '17 at 12:19
`Cells(lastUsedRow)` - you haven't provided a row and a column — Robin Mackenzie, Jun 22 '17 at 12:27
If I put a column it is still to the wrong cell. Different cell but still the wrong one. — Hydes Yase, Jun 22 '17 at 12:31
What does the `UserForm.TextBox1`have to do with the spreadsheet? Do you enter the name of a named range there, or what is its purpose? — codeguy, Jun 22 '17 at 13:07
`lastUsedRow = .Range(textboxValue).Rows.Count` - let's look at what this does. The `textboxValue` is used as a cell reference - probably the address of a range. `.Rows.Count` literally counts the number of rows that exist (empty or otherwise) in this range. So if the address in `textboxValue` for instance is `D8:F11` - which spans 4 rows (8, 9, 10 and 11), the returned value is 4 — CLR, Jun 22 '17 at 13:34
@CLR so is there a way to determine the last used row using a named range? — Hydes Yase, Jun 23 '17 at 07:41

score 0 · Answer 1 · answered Jun 22 '17 at 13:53

One possible way is this...

Dim rng As Range
Dim textboxValue As String, lastUsedRow As Long, i As Long
With ActiveSheet
    textboxValue = UserForm.TextBox1
    Set rng = .Range("textboxValue")
    For i = rng.Cells(1).Offset(rng.Cells.Count - 1).Row To rng.Cells(1).Row Step -1
        If .Cells(i, rng.Columns(1).Column) <> "" Then
            lastUsedRow = .Cells(i, rng.Columns(1).Column).Row
            MsgBox lastUsedRow
            .Cells(i, rng.Columns(1).Column).Select
            Exit Sub
        End If
    Next i
    If lastUsedRow = 0 Then
        MsgBox "textboxValue range is completely empty.", vbExclamation
    End If
End With

Excel VBA: Determine the last used row using a named range

1 Answers1