0

Been trying to get LastRow in a matter that I can use it in several codes in the same module.

At the moment this is what I got:

Function getLastRow() As Long

Dim LRow As Long
endrow = 11
Do Until Cells(endrow + 1, 1).Value = ""
    endrow = endrow + 1
Loop

getLastRow = LRow
End Function

The function is working but i get error 1004 on the line where i try to use it.

Sub PPP()

Application.ScreenUpdating = False
Call Unprotect
    ActiveSheet.Range("$A$11:$AH$" & getLastRow).AutoFilter Field:=4, Criteria1:="PPP" '<--error occurs here
Call Protect
Application.ScreenUpdating = True

End Sub

Appreciate any help with this, or even better solutions.

krib
  • 569
  • 4
  • 14
  • Do you always want to find the first blank in Column A? Or do you want to find the last used cell in Column A? Or are you trying to find the last used row in all columns? – Olly Aug 16 '17 at 10:58
  • 3
    Try `getLastRow = endrow` instead of `getLastRow = LRow`. – Mrig Aug 16 '17 at 10:58
  • @Olly yep. but could might as well be `...End(xlUp)` but did it this way since theres never a empty cell below row 11 – krib Aug 16 '17 at 11:02
  • @Mrig thanks. been editing this a few times that i forgot all about the `Dim LRow`. that solves it – krib Aug 16 '17 at 11:04
  • @krib you should also pass the `Worksheet` object, or the `Worksheet.Name` as a String to find the last row in the correct worksheet – Shai Rado Aug 16 '17 at 11:04
  • @ShaiRado yes, but this is constant and will always be this sheet for the end user. – krib Aug 16 '17 at 11:05

1 Answers1

1

This would work:

Function getlastrow(sht As Worksheet, Optional columnindex As Long = 1) As Long
   getlastrow = sht.Cells(sht.Rows.Count, columnindex).End(xlUp).Row
End Function

You can specify a sheet name or use ActiveSheet. You can also select a column but if one is not given it will preselect 1, i.e column A.

You'd use it like so:

ActiveSheet.Range("$A$11:$AH$" & getLastRow(ActiveSheet)).AutoFilter.....

Even better, this one assumes ActiveSheet if not preselected:

Function getlastrow(Optional sht As Worksheet = Nothing, Optional columnindex As Long = 1) As Long
   If sht Is Nothing Then Set sht = ActiveSheet
   getlastrow = sht.Cells(sht.Rows.Count, columnindex).End(xlUp).Row
End Function

You'd use this as per your original code like so:

ActiveSheet.Range("$A$11:$AH$" & getLastRow()).AutoFilter.....
CLR
  • 11,284
  • 1
  • 11
  • 29
  • this does the trick, a bit better than my first try and didn't mess up my hidden filterboxes. thx – krib Aug 16 '17 at 11:21
  • I've added another option, as I didn't like the `ActiveSheet` needing to be entered. – CLR Aug 16 '17 at 11:24
  • sweet. that's great. – krib Aug 16 '17 at 11:25
  • `Rows.Count` should be fully qualified `sht.Rows.Count` and please indent. Other than that excellent job +1. –  Aug 16 '17 at 11:54
  • @ThomasInzina - thanks, edited as per suggestion. Not sure I've ever qualified a `Rows.Count` when used in this way. Is there negative impact in not doing so? – CLR Aug 16 '17 at 13:05
  • 1
    It's a precaution in case an end user is working with the old .xls format and a new format. In this case, Rows.Count could cause either an Object defined error or possibly row 65,536 which is the max row for a xls file. The best Coders on SO will either fully qualify or not qualify at all. –  Aug 16 '17 at 13:21
  • Thanks, I'll keep it in mind going forward. – CLR Aug 16 '17 at 13:25