Monday, April 29, 2019

excel - Select multiple ranges with VBA



I need to select multiple ranges in a worksheet to run various VBA code on them. The ranges will always begin on row 84 but the end depends on how far down the data goes. I've been selecting these ranges separately using code like this:




Sub SelectRange()
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A84:B" & LastRow).Select
End Sub


That works fine, but I can't figure out how to select multiple ranges at once. I've tried everything I can think of:





  • Range("A84:B", "D84:E", "H84:J" & LastRow).Select

  • Range("A84:B,D84:E,H84:J" & LastRow).Select

  • Range("A84:B & LastRow,D84:E & LastRow,H84:J & LastRow").Select



Nothing works. I get a run-time error when running any of those.


Answer



Use UNION:




Dim rng as Range
With ActiveSheet
set rng = Union(.Range("A84:B" & LastRow),.Range("D84:E" & LastRow),.Range("H84:J" & LastRow))
End With
rng.select


But if you intend on doing something with that range then skip the .Select and just do what is wanted, ie rng.copy


No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...