I need to write a function to get dynamic range from some different sheets actually instead of repeating below codes in each macro:
LastRow = Sheetname.Cells(Rows.Count, 1).End(xlUp).Row
Set UserRange = Sheetname.Cells.Range("A2:A" & LastRow)
I like to have function like below:
Function GetRange(ByVal sht As Worksheet, rng As Range)
'do things ...
End Function
But I dont know how to pass a range to the function.
If you want to return a Range object using dynamic end row determination you will need to specify the function return as Range object and pass a start row variable and column number to use, in addition to using a last row method.
Option Explicit
Public Sub test()
Dim ws As Worksheet, rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = GetRange(ws, 1, 1)
Debug.Print rng.Address
End Sub
Public Function GetRange(ByVal ws As Worksheet, ByVal startRow As Long, ByVal columnNumber As Long) As Range
With ws
Set GetRange = Range(.Cells(startRow, columnNumber), .Cells(.Cells(.Rows.Count, columnNumber).End(xlUp).Row, columnNumber))
End With
End Function
You probably want some error handling/test that the determined end row is not less than the startRow e.g. with lastRow as 3 and startRow = 4
Option Explicit
Public Sub test()
Dim ws As Worksheet, rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = GetRange(ws, 4, 1)
If Not rng Is Nothing Then Debug.Print rng.Address
End Sub
Public Function GetRange(ByVal ws As Worksheet, ByVal startRow As Long, ByVal columnNumber As Long) As Range
Dim lastRow As Long
With ws
lastRow = .Cells(.Rows.Count, columnNumber).End(xlUp).Row
If Not lastRow < startRow Then
Set GetRange = .Range(.Cells(startRow, columnNumber), .Cells(lastRow, columnNumber))
End If
End With
End Function
just modified your code as below and its work:
Public Function GetRange(ByVal ws As Worksheet, ByVal StartRow As Long, ByVal FColumnNumber As Long, Optional ByVal LColumnNumber As Long) As Range
Dim LastRow As Long
With ws
LastRow = .Cells(.Rows.Count, FColumnNumber).End(xlUp).Row
If Not LastRow < StartRow Then
If LColumnNumber Then
Set GetRange = .Range(.Cells(StartRow, FColumnNumber), .Cells(LastRow, LColumnNumber))
Else
Set GetRange = .Range(.Cells(StartRow, FColumnNumber), .Cells(LastRow, FColumnNumber))
End If
End If
End With
End Function