Finding the last row within a Specific Range

Question

I need help with finding the last row in a worksheet range.

For example:

Worksheet name is MyWorksheet

Range is A1:A200

What im trying to find out is the row number of the last cell that has data in the MyWorksheet range A1:A200.

Possible duplicate of [How can I find last row that contains data in the Excel sheet with a macro?](https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) — AJD, Nov 15 '18 at 19:01

score 0 · Answer 1 · answered Nov 15 '18 at 18:37

0

Define your Range, Set it and use .Find

Here is a good example of what you are trying to accomplish.

Excel VBA Find row number of matching value

Hope this helps.

answered Nov 15 '18 at 18:37

Reese Jones

181
8

Hi Reese, the problem is that the range is relative to a specific tab. For example: I have an October, November and December Tab. If I define a range for say October, and I run the vba macro on November, the named range will not apply to November. I need vba to identify a particular sheet based on a specific range, and get the last row within the range A1:A200. – Mike Nov 15 '18 at 18:44
So you are trying to loop through each tab and find the last cell that has value? – Reese Jones Nov 15 '18 at 18:46
No, I'm trying to get the last row within a range of a specific tab. For example: Sheets("DumpTab").Range("D" & Rows.Count).End(xlUp).Row. This works fine to get the last row from a specific column; but what about if I wanted a specific range of data (A1:A200) and get the row number of the last cell with data. – Mike Nov 15 '18 at 18:49
You set the Range in that column. for example: WorkSheet.Range("A:A") then use the Range you defined to find what youre looking for in that column. https://stackoverflow.com/questions/40650508/excel-vba-find-last-row-in-range – Reese Jones Nov 15 '18 at 19:00

chris neilsen · Answer 2 · 2018-11-15T20:24:23.890

Taking into account all possible data layouts, inside and outside the range of interest requires consideration of a number of factors

In general, the range of interest may be of any size, anywhere on the sheet
There may be data adjacent to the range of interest, either above or below
The range of interest may be entirely empty
Is the "Last Row Number" required relative to the Range of Interest, or the Sheet
Note: this code treats cells containing an empty string as containing data

Function RangeLastRow(r As Range, _
  Optional Col = 1, _
  Optional RelativeToSheet As Boolean = False) As Long

    Dim rw As Long
    With r.Cells(r.Rows.Count, Col)
        If IsEmpty(.Value2) Then
            rw = .End(xlUp).Row
            If rw < r.Row Then
                rw = r.Row
            End If
        Else
            rw = r.Row + r.Rows.Count - 1
        End If
    End With
    If IsEmpty(r.Cells(rw - r.Row + 1, Col)) Then
        ' range is empty
        rw = 0
    Else
        If Not RelativeToSheet Then
            rw = rw - r.Row + 1
        End If
    End If
    RangeLastRow = rw
End Function

Use it as a UDF on a sheet, or in VBA like this

Sub Demo()
    Using default Column 1 and Relative to Sheet
    MsgBox RangeLastRow(Worksheets("MyWorksheet").Range("A1:A200"))
End Sub

Finding the last row within a Specific Range

2 Answers2