Dynamic VBA Function to Return Last Row or Last Column Dynamically.

This post contains two functions to return 1. Last Row and 2. Last Column with data. This will help you in identifying and manipulating range. The function is dynamic and you simple need to link it from your code.  

Please refer to the Usage Example provided. Copy and paste the all the code in to new module and test it out. Obviously, you'll have to change the sheet name and workbook name to suit your need. Please let me know if you need help :)

Below codes are tested & ready for copy-paste use.

Get the Last Row Dynamically (Function + Usage Example)

Option Explicit
'------------------------------------------------------GET LAST ROW
Function xlr(keyCol As String, Optional wbName As String, Optional shName As String) As Long

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'| Author: L Pamai (VbaA2z.Team@gmail.com)
'| Visit channel: Youtube.com/VbaA2z
'| More download: VbaA2z.Blogspot.com
'| Free for personal and commercial use. Unsupported product.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo errocc

If Len(wbName & vbNullString) > 0 And Len(shName & vbNullString) > 0 Then 'if wb name and sheet name specified
    If WbIsCurrentlyOpen(wbName) And SheetExists(shName, wbName) Then 'check if exist
        xlr = Workbooks(wbName).Sheets(shName).Range(keyCol & Rows.Count).End(xlUp).Row
    End If
    
ElseIf Len(shName & vbNullString) > 0 Then 'if Sheet name only specified
    If SheetExists(shName) Then
        xlr = Sheets(shName).Range(keyCol & Rows.Count).End(xlUp).Row
    End If
    
Else 'if wb name and sheet name NOT specified
    xlr = ActiveSheet.Range(keyCol & Rows.Count).End(xlUp).Row
End If

Exit Function

errocc:
xlr = 0

End Function

Sub usage_example_of_xlr_check()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'| Author: L Pamai (VbaA2z.Team@gmail.com)
'| Visit channel: Youtube.com/VbaA2z
'| More download: VbaA2z.Blogspot.com
'| Free for personal and commercial use. Unsupported product.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Dim lastrow As Long
    
    lastrow = xlr("B") 'get last row by column name specified
    'lastrow = xlr("B", , "Sheet1") 'get last row by column name and sheet name specified
    'lastrow = xlr("B", "bookName.xlsm", "Products") 'get last row by column name and sheet name and workbook name specified

    Sheets("Sheet1").Select
    Range("A1:A" & lastrow).Copy
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteValues
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
End Sub

Get Last Column Dynamically (Function + Usage Example)

'------------------------------------------------------GET LAST COLUMN
Function xlc(keyRow As String, Optional wbName As String, Optional shName As String) As Long

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'| Author: L Pamai (VbaA2z.Team@gmail.com)
'| Visit channel: Youtube.com/VbaA2z
'| More download: VbaA2z.Blogspot.com
'| Free for personal and commercial use. Unsupported product.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo errocc

If Len(wbName & vbNullString) > 0 And Len(shName & vbNullString) > 0 Then 'if wb name and sheet name specified
    If WbIsCurrentlyOpen(wbName) And SheetExists(shName, wbName) Then 'check if exist
        xlc = Workbooks(wbName).Sheets(shName).Cells(keyRow, Columns.Count).End(xlToLeft).Column
    End If
    
ElseIf Len(shName & vbNullString) > 0 Then 'if Sheet name only specified
    If SheetExists(shName) Then
        xlc = Sheets(shName).Cells(keyRow, Columns.Count).End(xlToLeft).Column
    End If
    
Else 'if wb name and sheet name NOT specified
    xlc = ActiveSheet.Cells(keyRow, Columns.Count).End(xlToLeft).Column
End If

Exit Function

errocc:
xlc = 0

End Function

Sub usage_example_of_xlc_check()
    Dim lastcol As Long
    
    'lastcol = xlc(2) 'get last column by row number specified
    'lastcol = xlc(2, , "Sheet1") 'get last column by row number and sheet name specified
    lastcol = xlc(2, "LRLCDynamics.xlsm", "Products") 'get last column by row number and sheet name and workbook name specified

    Sheets("Sheet1").Select
    
    Range(Cells(1, 1), Cells(100, lastcol)).Copy ' range A1 : Dynamic column row no 100;
    
    
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteValues
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
End Sub

Usage Example both  Dynamic Last Row and Dynamic Last Column

Sub usage_example_of_both_xlr_and_xlc_check()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'| Author: L Pamai (VbaA2z.Team@gmail.com)
'| Visit channel: Youtube.com/VbaA2z
'| More download: VbaA2z.Blogspot.com
'| Free for personal and commercial use. Unsupported product.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Dim lastcol As Long
    Dim lastrow As Long
    
    lastrow = xlr("B") 'get last row by column name specified
    'lastrow = xlr("B", , "Sheet1") 'get last row by column name and sheet name specified
    'lastrow = xlr("B", "b
    
    lastcol = xlc(2) 'get last column by row number specified
    'lastcol = xlc(2, , "Sheet1") 'get last column by row number and sheet name specified
    'lastcol = xlc(2, "LRLCDynamics.xlsm", "Products") 'get last column by row number and sheet name and workbook name specified

    Sheets("Sheet1").Select
    
    Range(Cells(1, 2), Cells(lastrow, lastcol)).Copy ' range A1 : Dynamic column row no dynamic lastrow;
    
    
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteValues
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
End Sub

Additional Functions to check if Workbook or Sheets refer to exist

Public Function SheetExists(shName As String, Optional wbName As String) As Boolean
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'| Author: L Pamai (VbaA2z.Team@gmail.com)
'| Visit channel: Youtube.com/VbaA2z
'| More download: VbaA2z.Blogspot.com
'| Free for personal and commercial use. Unsupported product.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Dim ws As Worksheet
    
    SheetExists = False
    
    On Error GoTo errocc
    If Len(wbName & vbNullString) > 0 Then
        Set ws = Workbooks(wbName).Sheets(shName)
        Else
        Set ws = Sheets(shName)
    End If
    
    Set ws = Nothing
    SheetExists = True
    Exit Function
errocc:
    SheetExists = False
End Function

Function WbIsCurrentlyOpen(strBookName As String) As Boolean
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'| Author: L Pamai (VbaA2z.Team@gmail.com)
'| Visit channel: Youtube.com/VbaA2z
'| More download: VbaA2z.Blogspot.com
'| Free for personal and commercial use. Unsupported product.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Dim oBk As Workbook
    On Error Resume Next
    Set oBk = Workbooks(strBookName)
    On Error GoTo 0
    If oBk Is Nothing Then
        WbIsCurrentlyOpen = False
    Else
        WbIsCurrentlyOpen = True
    End If
End Function