xldown to select multiple dynamic ranges

[sam1cannon]

Can anybody give the equivalent of using this

Range("A2:A22,D2:D22").Select

but by using a dynamic range. Want something like Range("a2:a2.end(xldown),d2:d2.end(xldown)).select

Thanks,

Sam

 

[pgc01]

Hi Sam
Welcome to the board

Try this code:

Sub Test()
Dim r As Range, rA As Range, rD As Range
 
Set rA = Range("A2", Range("A2").End(xlDown))
Set rD = Range("D2", Range("D2").End(xlDown))
Set r = Application.Union(rA, rD)
 
MsgBox "r address: " & r.Address
 
End Sub

Reamrk: You don't usually select objects in vba.