CODEWITHSUNDEEP
excelvbaexcel-2010
atame
atame

Reputation: 521

Find last row in range

I'm having a little trouble with finding the last row.

What I am trying to do is find the last row in column "A", then use that to find the last row within a range.

Example of Data:

Example of data

 1) LR_wbSelect = wbshtSelect.cells(Rows.count, "A").End(xlUp).Row - 22

 2) LR_wbSelectNew = wbshtSelect.cells(LR_wbSelect, "A").End(xlUp).Row

I am using the last row in column "A" as the data from row 29 down will always be the same length, the rows used in column "B" from row 29 can be a varying number of rows.

So I am trying to use LR_wbSelect in column "A" to get my starting last Row, then within LR_wbSelectNew using it as the starting point to look up from.

This works when the column I set to "A", LR_wbSelectNew gives me the row of "17", but when I change the column in LR_wbSelectNew to "B" it doesn't give the correct last row of "18".

I can change the column to "C, D, E, F" and the code works fine, but the only column that I can use is "B" because it will always have data in it, where the rest of that row could have a blank cell.

After doing some testing on the sheet, by pressing CRTL & Up from the lastring point of LR_wbSelect column "B" ignores the data in the rows and go to the row where it find data. I can't see a reason why Excel doesn't think there is data in these cells?

Upvotes: 10

Views: 105318

Answers (13)

Marcucciboy2
Marcucciboy2

Reputation: 3257

I came here looking for a way to find the last row in a non-contiguous range. Most responses here only check one column at a time so I created a few different functions to solve this problem. I will admit, though, that my .Find() implementation is essentially the same as Shai Rado's answer.

Implementation 1 - Uses Range().Find() in reverse order

Function LastRowInRange_Find(ByVal rng As Range) As Long
    
    'searches range from bottom up stopping when it finds anything (*)
    Dim rngFind As Range
    Set rngFind = rng.Find( What:="*", _
                            After:=rng.Parent.Cells(rng.row, rng.Column), _
                            LookAt:=xlWhole, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious)
                            
    If Not rngFind Is Nothing Then
        LastRowInRange_Find = rngFind.row
    Else
        LastRowInRange_Find = rng.row
    End If
    
End Function

Implementation 2 - Uses Range().End(xlUp) on each column

Function LastRowInRange_xlUp(ByVal rng As Range) As Long
    
    Dim lastRowCurrent As Long
    Dim lastRowBest As Long

    'loop through columns in range
    Dim i As Long
    For i = rng.Column To rng.Column + rng.Columns.count - 1
        If rng.Rows.count < Rows.count Then
            lastRowCurrent = Cells(rng.row + rng.Rows.count, i).End(xlUp).row
        Else
            lastRowCurrent = Cells(rng.Rows.count, i).End(xlUp).row
        End If
        
        If lastRowCurrent > lastRowBest Then
            lastRowBest = lastRowCurrent
        End If
    Next i
    
    If lastRowBest < rng.row Then
        LastRowInRange_xlUp = rng.row
    Else
        LastRowInRange_xlUp = lastRowBest
    End If
    
End Function

Implementation 3 - Loops through an Array in reverse order

Function LastRowInRange_Array(ByVal rng As Range) As Long

    'store range's data as an array
    Dim rngValues As Variant
    rngValues = rng.Value2

    Dim lastRow As Long
    
    Dim i As Long
    Dim j As Long

    'loop through range from left to right and from bottom upwards
    For i = LBound(rngValues, 2) To UBound(rngValues, 2)                'columns
        For j = UBound(rngValues, 1) To LBound(rngValues, 1) Step -1    'rows

            'if cell is not empty
            If Len(Trim(rngValues(j, i))) > 0 Then
                If j > lastRow Then lastRow = j

                Exit For
            End If

        Next j
    Next i

    If lastRow = 0 Then
        LastRowInRange_Array = rng.row
    Else
        LastRowInRange_Array = lastRow + rng.row - 1
    End If
    
End Function

I have not tested which of these implementations works fastest on large sets of data, but I would imagine that the winner would be _Array since it is not looping through each cell on the sheet individually but instead loops through the data stored in memory. However, I have included all 3 for variety :)

How to use

To use these functions, you drop them into your code sheet/module, specify a range as their parameter, and then they will return the "lowest" filled row within that range.

Here's how you can use any of them to solve the initial problem that was asked:

Sub answer()

    Dim testRange As Range
    Set testRange = Range("A1:F28")
    
    MsgBox LastRowInRange_Find(testRange)
    MsgBox LastRowInRange_xlUp(testRange)
    MsgBox LastRowInRange_Array(testRange)
    
End Sub

Each of these will return 18.

Upvotes: 3

MrMatt
MrMatt

Reputation: 1

Backing off from the range to the worksheet will get you the whole sheet extents of the range used on the sheet (which may be smaller than you expect if the sheet doesn't have data in the top rows; but it does include internal blanks)

TheRange.Worksheet.UsedRange.Rows.Count

If there is no data in the top rows, the following will get you the first row which you need to add to the above to get the highest row number

TheRange.End(xlDown).Row

So Dim TheRange as Range Dim MaxRow as Long MaxRow = TheRange.Worksheet.UsedRange.Rows.Count + TheRange.End(xlDown).Row

Will get the highest row number with data (but not the whole sheet)

Upvotes: 0

user17488174
user17488174

Reputation: 11

Simple function that return last row no. in specific sheet. It takes the last address in UsedRange and retrieve last row number. Feel to free change the code and use standard range insead of UsedRange.

Function FindLastRow(wsToCheck As Worksheet) As Long
    Dim str As String
    str = wsToCheck.UsedRange.AddressLocal()
    FindLastRow = Right(str, InStr(1, StrReverse(str), "$") - 1)
End Function

Upvotes: 1

cssyphus
cssyphus

Reputation: 40106

Shai Rado's first solution is a great one, but for some it might need a bit more elaboration:

 Dim rngCurr, lastRow
 rngCurr = wbshtSelect.Range("B10").CurrentRegion
 lastRow = rngCurr.Rows(rngCurr.Rows.Count).Row

If you want to know the last used row in the entire worksheet:

 Dim rngCurr, lastRow
 rngCurr = Range("A1").CurrentRegion
 lastRow = rngCurr.Rows(rngCurr.Rows.Count).Row

Upvotes: 0

Louis
Louis

Reputation: 49

Before getting into complex coding why not build something on the below principle:

MaxRow = Application.Evaluate("MIN(ROW(A10:C29)) + ROWS(A10:C29) - 1")

Upvotes: -1

adofil
adofil

Reputation: 11

Dim rng As Range
Dim FirstRow, LastRow As long

Set rng = Selection

With rng

 FirstRow = ActiveCell.Row

 LastRow = .Rows(.Rows.Count).Row

End With

Upvotes: 0

Jayant Kumar jain
Jayant Kumar jain

Reputation: 27

    'This is sure method to find or catch last row in any column even   'if  some cell are blank in-between. (Excel-2007)` 
'This works even if sheet is not active

    'mycol is the column you want to get last row number

for n=1048575 to 1 step -1
myval=cells(n,mycol)
if myval<>"" then
mylastrow=n 'this is last row in the column
exit for
end if
next

ret=msgbox("Last row in column-" & mycol & "is=" & mylastrow)

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

There are mulitple results and methods when searching for the LastRow (in Column B).

When using Cells(.Rows.Count, "B").End(xlUp).Row you will get the last row with data in Column B (it ignores rows with spaces, and goes all the way down).

When using:

 With wbshtSelect.Range("B10").CurrentRegion
     LR_wbSelectNew = .Rows(.Rows.Count).Row
 End With

You are searching for the last row with data in Column B of the CurrentRegion, that starts from cell B10, untill the first line without data (it stops on the first row with empty row).

Full Code:

Sub GetLastRow()

Dim wbshtSelect         As Worksheet
Dim LR_wbSelectNew      As Long

' modify "Sheet2" to your sheet's name
Set wbshtSelect = Sheets("Sheet2")

' find last row with data in Column B
With wbshtSelect
    LR_wbSelectNew = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
' for debug only
Debug.Print LR_wbSelectNew ' >>result 31

' find last row with data in Column B at current regioun starting at cell B10
With wbshtSelect.Range("B10").CurrentRegion
    LR_wbSelectNew = .Rows(.Rows.Count).Row
End With
' for debug only
Debug.Print LR_wbSelectNew ' >> result 18

End Sub

Edit1: code searches for last row for cells with values (it ignores blank cells with formulas inside).

Sub GetLastRow()

Dim wbshtSelect         As Worksheet
Dim LR_wbSelectNew      As Long

' modify "Sheet2" to your sheet's name
Set wbshtSelect = Sheets("Sheet2")

' find last row with data in Column B at current regioun starting at cell B10
With wbshtSelect.Range("B10").CurrentRegion
    LR_wbSelectNew = .Rows(.Rows.Count).Row
End With

Dim Rng         As Range    
Set Rng = wbshtSelect.Range("B10:B" & LR_wbSelectNew)

' find last row inside the range, ignore values inside formulas
LR_wbSelectNew = Rng.Find(What:="*", _
                    After:=Range("B10"), _
                    LookAt:=xlPart, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

' for debug
Debug.Print LR_wbSelectNew  ' << result 18 (with formulas in the range)

End Sub

Upvotes: 19

Punith Gubbi
Punith Gubbi

Reputation: 692

LR_wbSelect = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Upvotes: 1

user6432984
user6432984

Reputation:

Range().End will bring you to the end of a code block. If the starting cell is empty, it brings you the the first used cell or the last cell. It the cells is not empty it brings you to the last used cell. For this reason, you need to test whether or not the cell in column B is to determine whether to use LR_wbSelectNew as the last row. 

With wbshtSelect
    LR_wbSelect = .Cells(Rows.Count, "A").End(xlUp).Row - 22

    If .Cells(LR_wbSelect, "B") <> "" Then
        LR_wbSelectNew = LR_wbSelect
    Else
        LR_wbSelectNew = .Cells(LR_wbSelect, "B").End(xlUp).Row
    End If
End With

This code defines a Target range that extends from A1 to the last row in column a - 22 and extends 10 columns.

Dim Target As Range
With wbshtSelect
    Set Target = .Range("A1", .Cells(Rows.Count, "A").End(xlUp).Offset(-22)).Resize(, 10)
End With

Upvotes: 0

ArindamD
ArindamD

Reputation: 241

LR_wbSelectNew = wbshtSelect.cells(LR_wbSelect, "B").End(xlUp).Row

Why are you using "LR_wbSelect" as the row counter? If you want to know the last row of column 'B', you should use Rows.count

Rows.count --> Returns maximum number of rows (which is 1048576 for Excel 2007 and up) End(xlUp) --> Moves the pointer upward to the last used row

So, cells(Rows.count, "A").End(xlUp).Row --> This moves the pointer to the last row if the column 'A' (as if you are pressing Crtl+Up keys when A1048576 cell is selected)

So, use Rows.count to select the last row for column 'B' as well. If you have some specific requirement related to LR_wbSelect, please mention it.

Alternatively, if you want to know the last row used in a sheet, you may use the below:

mySheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Upvotes: 2

Niclas
Niclas

Reputation: 1262

If your wbshtSelect is defined as worksheet and you have used set to define the specific worksheet, you can use this.

 Dim LastRow As Long

 wbshtSelect.UsedRange ' Refresh UsedRange
 LastRow = wbshtSelect.UsedRange.Rows(wbshtSelect.UsedRange.Rows.Count).Row

Otherwise take a look here http://www.ozgrid.com/VBA/ExcelRanges.htm

Upvotes: 2

Rockstar
Rockstar

Reputation: 2288

Hope this piece of code helps !

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

Upvotes: 4

Related Questions