In many procedures from the Excel VBA Add-ins or other macros that I create, 2 common needs are to calculate the last used row, last column of a worksheet and to check if the user selected a Range or other object.
- The last row and column are needed because in most of the procedures we “put the code” to “do something” with all the data, until it reaches the last row and/or column.
- The TypeName of selections in needed because if he selected something else than a Range, the procedure must give a notification and exit. If he selected a Range the procedure can continue. This need appears because the macros are designed to “do something” with the values from the selected range and we don’t want to get an uncontrolled error in the situation when the user selected for example a chart axis and runs our macro.
It’s not a good practice to copy the same code block in different procedures because if in the future you need to modify something at that code you will have to search and modify in all the places where you copied it. In most cases like this, the best approach is to create in a separate module some functions which will check what you need. Then, you can call those functions in all other procedures where you need them. This way, if someone else or even you will need to modify the logic of that code, you will have to modify it only in one function, not in all other procedures which calls it. Also, this way your code will have consistency (you are always using the same “old and tested” method to get the same result !)
Enough with the theory, let’s see the facts.
- Here it is my solution for calculating the Last Row, Last Column, First Row and First Column for the used range in a given worksheet:
Function findLrUsedRange(sh As Worksheet, lr As Long, Optional lc As Integer, Optional fr As Integer, Optional fc As Integer) 'if the sheet is filtered, clear the filter On Error Resume Next sh.ShowAllData On Error GoTo 0 'Check if there is at least one used cell If WorksheetFunction.CountA(sh.Cells) > 0 Then 'first row: fr = sh.Cells.Find(What:="*", After:=ActiveSheet.Cells(sh.Rows.Count, sh.Columns.Count), _ SearchDirection:=xlNext, SearchOrder:=xlByRows).Row 'first column: fc = sh.Cells.Find("*", After:=ActiveSheet.Cells(sh.Rows.Count, sh.Columns.Count), _ SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column 'last row lr = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'last column lc = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column Else fr = 0 fc = 0 lr = 0 lc = 0 End If End Function
- The Last Row is a mandatory parameter because in 99% percent of my macros I need it. It is defined as Long, because it can have greater values than the other parameters for which the set of Integers is enough.
- You can call this function in every procedure where you need it, like in the below example:
Sub testLastRow() Dim lr As Long Dim lc As Integer Call findLrUsedRange(ActiveSheet, lr, lc) MsgBox "The number of Last Row of used Range is: " & lr & vbNewLine & vbNewLine & "The Last column of the used Range is: " & lc, vbInformation 'if we want, when we call the function we can put also the other optional parametrs to find out also the first row and first column of the used range !! 'they must be declared first !!! '..... procedure....., End Sub
- The Result:
- The solution for TypeName of the Selection is the following:
Function rangeSelection(output_mess As String) As Boolean Dim r As Object Set r = Selection If TypeName(r) <> "Range" Then output_mess = "The selection is not a range.. It is a: " & TypeName(r) & ". Please select a range !" rangeSelection = False Else rangeSelection = True End If End Function
- We can use the function in our procedures like below (notice that if we call it in 50 other procedures for example, and at a given moment we decide to change the message received by user in case the selection is not a Range, we have to change the message only once, in our function and the update will be available immediately in all 50 procedures):
Sub testSelectionType() Dim mess As String, x As String If rangeSelection(mess) = False Then MsgBox mess, vbExclamation Exit Sub End If 'replace this with your code to be executed if selection is a range" x = MsgBox("The selection is a Range" & vbNewLine & vbNewLine & "Your code can continue", vbExclamation, "Test") End Sub
- The Result:
– If you select an Axis of a Chart:
– If you select a TextBox:
– If you select a Range:
You can also download the file with the model of this working procedures and all the used code here: