VBA LastRow calculation not working

VBA LastRow calculation not working

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 by Greg R for VBA LastRow calculation not working

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 by user2063626 for VBA LastRow calculation not working

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 by Our Man In Bananas for VBA LastRow calculation not working

how about:

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

hth Philip

Answer by GetUserName for VBA LastRow calculation not working

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 by brettdj for VBA LastRow calculation not working

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 by JoeWalt for VBA LastRow calculation not working

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 by user6651685 for VBA LastRow calculation not working

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.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72