Edison.X. Blog

假設每二行空一行選取，二種方式，較好的方式還是設別名較佳。

' ---------------------------------------------------------------
' method1, don't use typedef range
Function MySelect1() As Range
    Dim i, LastRow As Long
    LastRow = ActiveSheet.UsedRange.Rows.Count
   
    Dim All, pre_all, cur As Range
    Set pre_all = Range("B2", "F3")
   
    For i = 5 To LastRow Step 3
        Set cur = Range("B" & i, "F" & (i + 1))
        Set All = Union(cur, pre_all)
        Set pre_all = All
    Next i
   
    pre_all.Select
    Set MySelect2 = pre_all
End Function

' ---------------------------------------------------------------
' method2, use typedef range
Function MySelect2()
    Dim i, LastRow As Long
    Dim MyArea() As Range
   
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ReDim MyArea(LastRow / 3 + 1) As Range
   
    ' define range name
    For i = 2 To LastRow Step 3
        Set MyArea((i - 2) / 3) = Range("B" & i, "F" & (i + 1))
    Next i
   
    ' select range
    Dim rgn1, rgn2, tmp As Range
    Set rgn1 = MyArea(0)
   
    For i = 1 To (LastRow / 3 - 1)
        Set rgn2 = Union(MyArea(i), rgn1)
        Set rgn1 = rgn2
    Next i
   
    rgn1.Select
    Set MySelect1 = rgn1
End Function