Finding Last Row using Excel VBA

Question

I have an inquiry on how to find the last row in excel vba. I am currently working on a project which requires me to find the last row of this particular worksheet called Annex 1A.

A snip image of the worksheet is shown below:

Click Here For Image

For instance, from the image above, Row 32 and Row 33 contain empty values and I would like to derive the total of rows that is being used.

I have tried this following method :

Method 1

LastRow = Sheets("Annex 1A").Cells.Find(What:="*", _
                  After:=Sheets("Annex 1A").Range("B1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row

Method 2

  LastRow = Sheets("Annex 1A").Range("B" & Sheets("Annex1A").Rows.Count).End(xlUp).Row

The LastRow value would always return 31 instead of 33. Is there any other way for me to derive the value of 33?

[This article may answer your questions](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Forward Ed, May 09 '16 at 04:16
@ForwardEd I have looked through this article for reference but it still doesnt get the value that I need :( but thanks for the suggestion ! — MysteryGirl, May 09 '16 at 05:35

score 0 · Accepted Answer · answered May 09 '16 at 05:25

0

This method has worked for me in like 90% of the times I needed it:

Lastrow = Sheets("Annex 1A").UsedRange.SpecialCells(xlCellTypeLastCell).row

answered May 09 '16 at 05:25

gizlmo

1,882
1
14
14

Thanks @gizlmeier! I have finally derived the 33 as a value by using ur method :) ! – MysteryGirl May 09 '16 at 05:33

score 0 · Answer 2 · answered May 09 '16 at 05:49

I know you've already accepted an answer but this might be useful to others.

UsedRange is often eschewed as it can be unreliable. However, in this case it does have a place and may do the trick for you.

However, if you wish to regain full control of UsedRange then a method which combines UsedRange with some bespoke cell checking could be the answer. An example in your case, might be one which looks for a either a non empty cell or one that is shaded.

Public Function BespokeFindLastRow() As Long
    Dim rng As Range
    Dim i As Long

    'Use the intersect function to find column B
    'as column references of UsedRange are relative
    'so if nothing is in column "A", then
    'column(1) or ("A") in your UsedRange would
    'actually be column "B".
    With ThisWorkbook.Worksheets("Sheet1")
        Set rng = Intersect(.UsedRange, .Columns("B"))
    End With

    'Return -1 if no cells are found
    If rng Is Nothing Then
        BespokeFindLastRow = -1
        Exit Function
    End If

    'Loop backwards through the cells in the column range
    'to find either a non-empty cell or a coloured cell.
    For i = rng.Cells.Count To 1 Step -1
        With rng.Cells
            If Not IsEmpty(.Item(i)) Or .Interior.ColorIndex <> xlNone Then
                BespokeFindLastRow = .Row
                Exit Function
            End If
        End With
    Next

    'Nothing was found in the column so return -1
    BespokeFindLastRow = -1
End Function

can this sub also be modified to get the lastRow without specifying the column? I like that code because it returns "0" if there is no value in the column. — smartini, Jun 03 '20 at 10:58
@smartini, we tend not to answer extension questions within the same question, so that future people finding this question see a single question and answer. If you post a new question, I, or others, would be happy to answer though. — Ambie, Jun 06 '20 at 09:19

Finding Last Row using Excel VBA

2 Answers2

Linked