How to select multiple, relative cells in VBA-CodePudding

A

Range("O16:P19").Select

This code will select a single, grid-locked area

B

Range("Q19:R22,T19:U22").Select

This code is able to select multiple, grid-locked areas

C

Range(ActiveCell.Offset(0,0), ActiveCell.Offset(1,1)).Select

This code is able to select a single, relative area

D ?????

How do you write code to select multiple, relative areas in VBA

If 'A' is to 'B' as 'C' is to 'D', then how do I write 'D'?

CodePudding user response：

You can use Union to join a bunch of different ranges.
Here you can see I'm mixing relative ranges and absolute references:

Sub Teste()
    Dim RG As Range
    Set RG = Union( _
        ActiveCell.Offset(0, 0).Resize(3, 3), _
        ActiveCell.Offset(5, 5).Resize(2, 2), _
        ActiveCell.Offset(10, 2).Resize(1, 5), _
        Range("A2:B3"))
    RG.Select
    ActiveCell.Activate
End Sub

CodePudding user response：

Relative Ranges

Offset

Sub Test1()
 
    Dim ColumnOffset As Long
    ColumnOffset = Columns("D").Column - Columns("A").Column
    
    Dim rg As Range: Set rg = Columns("F").Offset(, ColumnOffset)
    
    Debug.Print rg.Address ' Result: '$I:$I'

End Sub

Resize

Sub Test2()

    Dim srg As Range: Set srg = Columns("A:D")
    Dim drg As Range: Set drg = Columns("F").Resize(, srg.Columns.Count)
    
    Debug.Print drg.Address ' Result: '$F:$I'

End Sub

Sub Test3()

    Dim srg As Range: Set srg = Range("A1:E5")
    Dim dfCell As Range: Set dfCell = Range("C10")
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    Debug.Print drg.Address ' Result: '$C$10:$G$14'

End Sub

Sub Test4()

    Dim srg As Range: Set srg = Range("A1:E5")
    Dim dfCell As Range: Set dfCell = Range("C10")
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    Dim urg As Range: Set urg = Union(srg, drg)
    
    Debug.Print urg.Address ' Result: '$A$1:$E$5,$C$10:$G$14'

End Sub