选择单元格到命名范围VBA中最后填充的行

我有一个命名范围,例如GroupedCells:Sheet1 $ C $ 1,Sheet1 $ D $ 1,Sheet1 $ G $ 1。我想将这些行中的数据保存到最后填充的行。

码:

Sheets("Sheet1").Select
Range("GroupedCells").Select
Range(Selection, Selection.End(xlDown)).Select

它不起作用,因为它仅选择Sheet1 $ C $ 1到Sheet1 $ C $ 10。我需要它选择Sheet1 $ C $ 1:Sheet1 $ C $ 10,Sheet1 $ D $ 1:Sheet1 $ D $ 10,Sheet1 $ G $ 1:Sheet1 $ G $ 10。我如何使用Namedrange。

评论
伊~小夏
伊~小夏

请尝试以下代码:

Sub testSelectNamedRange()
      Dim rng As Range, lastRow As Long, rng1 As Range
      Dim rng2 As Range, rng3 As Range, arrRng As Variant
       Set rng = Range("GroupedCells")  'put the named range in a variable
       arrRng = Split(rng.address, ",") 'extract each cell addres from the above range
       lastRow = Cells(Rows.count, rng.Column).End(xlUp).Row 'determine the last row of the first rng column
       Set rng1 = Range(Range(arrRng(0)), Cells(lastRow, Range(arrRng(0)).Column)) 'create a range for each column
       Set rng2 = Range(Range(arrRng(1)), Cells(lastRow, Range(arrRng(1)).Column))
       Set rng3 = Range(Range(arrRng(2)), Cells(lastRow, Range(arrRng(2)).Column))
       Union(rng1, rng2, rng3).Select 'select the union of the above created ranges
End Sub
点赞
评论
enemo
enemo

尝试,

Sub test2()
    Dim rngDB As Range
    Dim rngU As Range
    Dim ar As Range
    Set rngDB = Range("GroupedCells")

    For Each ar In rngDB.Areas
        If rngU Is Nothing Then
            Set rngU = Range(ar, ar.End(xlDown))
        Else
            Set rngU = Union(rngU, Range(ar, ar.End(xlDown)))
        End If
    Next
    If rngU Is Nothing Then
    Else
        rngU.Select
    End If
End Sub
点赞
评论