在excel VBA中获取范围函数

debugcn 发表于 Dev

阿瓦克斯

我需要编写一个函数来实际从一些不同的工作表中获取动态范围，而不是在每个宏中重复以下代码：

LastRow = Sheetname.Cells(Rows.Count, 1).End(xlUp).Row

Set UserRange = Sheetname.Cells.Range("A2:A" & LastRow)

我喜欢有如下功能：

Function GetRange(ByVal sht As Worksheet, rng As Range)

    'do things ...

End Function

但我不知道如何将范围传递给函数。

哈尔

如果要使用动态结束行确定返回 Range 对象，除了使用最后一行方法之外，您还需要指定函数 return 为 Range 对象并传递要使用的起始行变量和列号。

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

您可能想要一些错误处理/测试，确定的结束行不小于startRow例如lastRowas 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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。