Lastrow Function instead of always typing out the count

[paldob]

Hi All,

I always find myself typing the following for getting the Lastrow when required

ThisWorkbook.Sheets("xxx").Cells(Rows.Count, x).End(xlUp).Row

However, I have been learning to create functions recently, and I have been messing around and created the below

Function GetLastRow(ws As Worksheet, Target As Variant)


'This can be used to call (example below)
GetLastRow = ws.Cells(Rows.Count, Target).End(xlUp).Row


End Function

This could be used as follows

Lastrow = GetLastRow(ThisWorkbook.Sheets("Sheet1"), 1)

Do you think the above function would come in handy, if I am using the Lastrow count in multiple Sub Routines and Modules?

 

[My Aswer Is This]

If your trying to get the Lastrow of the active workbook and the active sheet you do not need to include Thisworkbook and sheet name.

 

[Rick Rothstein]

Here is another version of your function (note that I changed the name) that you may want to consider. The first argument is the column letter designation or the column number (your choice). The second argument is optional... if omitted, the active worksheet name is used, otherwise simply pass the name of the sheet in. The third argument is also optional... if omitted, the active workbook is used, otherwise simply pass in the workbook's name (remember to include the file extension as well). So, if you wanted to find the last row in Column M on the active sheet (in the active workbook, of course), you would use this...

=LastRow("M")

If, instead, you wanted the last row in Column M on a sheet named, say, "Other Data", (again, in the active workbook) you would use this formula...

=LastRow("M", "Other Data")

If, instead, you wanted the last row in Column M on a sheet named, say, "Other Data" in a workbook named "Kishan.xlsm, you would use this formula...

=LastRow("M", "Other Data", "Kishan.xlsm")

Here is the code...

[table="width: 500"]
[tr]
	[td]Function LastRow(Col As Variant, Optional SheetName As String, Optional WorkbookName As String) As Long
  Dim WS As String, WB As Workbook
  If WorkbookName = "" Then
    Set WB = ActiveWorkbook
  Else
    Set WB = Workbooks(WorkbookName)
  End If
  If SheetName = "" Then
    WS = WB.ActiveSheet.Name
  Else
    WS = SheetName
  End If
  LastRow = WB.Sheets(WS).Cells(Rows.Count, Col).End(xlUp).Row
End Function[/td]
[/tr]
[/table]

 

[paldob]

Wow totally blown away. Just when I start to feel proud of myself for learning something so simple! I certainly have a long way to go :D

Thank you very much.

 

[Rick Rothstein]

Wow totally blown away.

The idea behind the code I write is to minimize the amount of "work" the user has to do to use it. In other words, do as much as you can in the code so the user does not have to do it later when he/she goes to use it.

Just when I start to feel proud of myself for learning something so simple! I certainly have a long way to go :D

Just so you know, I have been writing code (in BASIC and the compiled version of Visual Basic, the predecessors to VBA) daily since I bought my first computer in 1981, so I have a few years of experience on you. Keep writing code and reading other people's code here on this forum and it will all start to "click" in short order.

Thank you very much.

You are quite welcome.

 

[JumboCactuar]

@Rick Rothstein great function

Is code from functions available to all modules? Example if I have a module named myFunctions with all UDFs

Could I call yours from another module like:

Dim WB1 as Workbook
Dim WS1 as Worksheet

Set WB1 = thisworkbook
Set WS1 = WB1.Sheets("data")

DatasheetLastRow = LastRow("M",WS1,WB1)