VBA LastRow calculation not working

Question

I have a worksheet with an autofiltered range that starts in cell B3. Column A contains some macro buttons but is effectively blank. The top two rows contain information about the data in the main range.

In VBA, I am using what I believe is a standard method for determining the last row in a worksheet (In this case I cannot rely on the .End method on a single column):

LastRow = Activesheet.Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

However, sometimes this returns a value of one, even when I have thousands of rows of data. It seems to only do this when there are filters set (but there are still visible rows with data in them), but even then it doesn't always happen and I can't see a pattern to it.

I know there are other solutions - I have changed to a UsedRange technique instead, but it is very frustrating that this particular one fails as it would otherwise be the most effective one in this situation.

Does anyone know why this would be happening?

Answer 1

Have you thought of using Greg's answer, but looped to find the highest row of all the columns? Something like:

LastRow = 1
With ActiveSheet
   For i = 1 to .UsedRange.Columns.Count
      If .Cells(.Rows.Count, i).End(xlUp).Row > LastRow Then
         LastRow = .Cells(.Rows.Count, i).End(xlUp).Row
      EndIf
   Next i
End With

This solution would allow for blank values randomly populated in bottom rows. UsedRange is tricky as it will return the furthest outlying row/column that has ever been edited (even if it is currently blank). In my experience Range.End(xlUp) behaves as you would expect if you pressed Ctrl-Up while in a worksheet. This is a little more predictable.

If you are set on using .Find try looking into the After:=[A1] argument. I haven't explored the idiosyncrasies of this function, but that would be the place I'd start given this problem.

Answer 2

try this...

Dim LastRow as long

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

This will get the last row in column A.

Answer 3

Suggest that you try using Find specifying to lookin XlFormulas as unlike XlValues, hidden cells (but not filtered cells) will be detected with this argument.

Sub Test()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row
End Sub

Answer 4

I faced the exact same issue this morning.

At first, I was convinced that ".find" feature is unstable.

But, after fiddling for a while, I found out a non-empty cell at a row number way too deep in my Sheet, think it was 1,000 or 10,000 or similar. I deleted it and ".find" works again. Probably, the limits of certain internal VBA variables are not big enough.

Do this:

1) Press CTRL + END

2) Identify the non-empty cell(s), assuming this is inadvertently filled in, and delete it.

Answer 5

Try below code :

Sub GetColA_LastRow()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With

    MsgBox "The last row which has data in Col A of Sheet1 is " & lRow
End Sub

OR

sub getLastRow()
 dim lastRow as long
 lastRow = Sheets("sheet1").Range("A65000").End(xlUp).Row

end sub

you may also visit the link for more details http://www.siddharthrout.com/2012/10/02/find-last-row-in-an-excel-sheetvbavb-net/

Update the code after comments :

Sub getLastRow()

    Dim rng As Range, lastRow As Long
    Set rng = Cells.Find("mango") ' here you enter whatever you want to find

    If Not rng Is Nothing Then
        lastRow = Sheets("sheet1").Cells(65000, rng.Column).End(xlUp).Row
    End If

End Sub

Answer 6

how about:

with activesheet.usedrange
    LastRow = .rows.count
end with

hth Philip

Answer 7

My similar question was what's the last row and col used regardless of empty cells before with or without filtering. I cobbled this together from bits and pieces I could find and it does what I think both you and I want, at least for cells populated with data.

Function FindLastUsedRowAndCol(ByVal ws As Worksheet) As Variant()

Dim LastColRange As Range
Dim LastCol As Integer
Dim LastRow As Long
Dim LastRowTmp As Long
Dim RowXCol(2) As Variant


Set LastColRange = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
LastCol = LastColRange.Column

LastRow = 1

For i = 1 To LastCol Step 1
    If ws.FilterMode Then
        LastRow = ws.AutoFilter.Range.Rows.Count
        LastRowTmp = Cells(ws.Rows.Count, i).End(xlUp).row
        If LastRowTmp > LastRow Then LastRow = LastRowTmp
    Else
        LastRowTmp = Cells(ws.Rows.Count, i).End(xlUp).row
        If LastRowTmp > LastRow Then LastRow = LastRowTmp
    End If
Next i

RowXCol(1) = LastRow
RowXCol(2) = LastCol
FindLastUsedRowAndCol = RowXCol

End Function

And to test:

Sub testit()
Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet1")
cr = FindLastUsedRowAndCol(ws)
MsgBox "row: " & cr(1) & " col: " & cr(2)
End Sub

Answer 8

I know this is an old post but i have seen exactly this problem and not seen any answers that deal with the issue. It seems to occur sometimes on a dataset where there are rows hidden immediately after the last row. It doesn't matter whether you set to lookin xlformulas or xlvalues and i have tried every permutation of the find command i can find and it persistently returns the value 1. (As OP says) The solutions above don't fix this. I had to create a function which iterates to find the last row in this case (key bit of code below - in my case i needed to find lastrow in first two columns of various datasheets):

On Error GoTo ExitLoop
StartRow = 1
LastRow = .Columns("A:B").Find(What:="*", SearchDirection:=xlNormal, LookIn:=xlValues, SearchOrder:=xlByRows).Row
StartRow = LastRow + 1
Do Until WorksheetFunction.CountA(.Range(.Cells(StartRow, 1), .Cells(1048576, 2))) = 0
    FindLastRow = .Range(.Cells(StartRow, 1), .Cells(1048576, 2)).Find(What:="*", SearchDirection:=xlNormal, LookIn:=xlValues, SearchOrder:=xlByRows).Row
    StartRow = LastRow + 1
Loop
ExitLoop: