VBA LastRow calculation not working

Question

I have a worksheet with an autofiltered range that starts in cell B3 . Column A contains several macros, but is actually empty. The upper two lines contain information about the data in the main range.

In VBA, I use what I consider to be the standard method for determining the last row in a worksheet (in this case, I cannot rely on the .End method in one column):

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

However, sometimes this returns the value of one, even if I have thousands of rows of data. It seems that this happens only when filters are installed (but they still have visible rows with data), but even then this does not always happen, and I do not see a template in it.

I know that there are other solutions - instead, I changed the UsedRange method, but it is very difficult that this particular one fails, because otherwise it would be most effective in this situation.

Does anyone know why this is happening?

Answer 1

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

 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 will allow the use of empty values ​​randomly filled in the bottom lines. UsedRange is tricky as it will return the farthest deleted row / column that has ever been edited (even if it is currently empty). In my experience, Range.End (xlUp) behaves as you would expect if you pressed Ctrl-Up on a worksheet. This is a little predictable.

If you are configured to use .Find, try examining the After: = [A1] argument. I did not study the features of this function, but this would be the place where I would start asking this problem.

Answer 2

try it...

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

This will result in the last row in column A.

Answer 3

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

 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

This morning I came across the same question.

At first I made sure that the ".find" function is unstable.

But after some time, I found a non-empty cell with the line number too deep in my sheet, I think it is 1000 or 10000 or something like that. I deleted it and ".find" works again. Probably, the limits of some internal VBA variables are not large enough.

Do it:

1) Press CTRL + END

2) Define a non-empty cell (s), suggesting that it is inadvertently filled and will delete it.

Answer 5

Try entering the 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 can also visit the link for more information http://www.siddharthrout.com/2012/10/02/find-last-row-in-an-excel-sheetvbavb-net/

Update the code after the 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

What about:

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

NTN Philip

Answer 7

My similar question was that the last rows and columns were used independently of empty cells, before with or without filtering. I put it together from pieces and pieces that I could find, and it does what I think of both you and me, at least for the cells filled 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 check:

 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 saw exactly this problem and did not see the answers regarding this problem. It seems that this sometimes happens in a dataset where there are rows hidden immediately after the last row. It doesn't matter if you are configured for xlformulas or xlvalues, and I tried every permutation of the find command that I could find, and it constantly returns 1. (As OP says). The above solutions do not fix this. I had to create a function that iterates to find the last row in this case (the key code snippet is below - in my case I needed to find lastrow in the first two columns of different data tables):

 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: