Excel VBA- Find the Last Row and the selection Type Name

0 Shares

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