Be the first user to complete this post

  • 0
Add to List

VBA Excel – Refer to Multiple Ranges : Union Method

You can combine more than one Ranges into one Range using Union Method and later on instead of writing all the ranges repeatedly, just use the combined one.

Example

Sub CombileMultipleRange()

   Dim objCombinedR as Range

   Set R1 = Sheet1.Range("H1")   

   Set R2 = Sheet1.Range("M2")

   Set objCombinedR = Union(R1, R2)

 objCombinedR.Interior.ColorIndex = 44

End Sub

Similarly you can combine multiple rows or columns into one Range

Sub CombileMultipleRows()

Dim objCombinedR as Range

Set R1 = Sheet1.Range(Rows(1))   

Set R2 = Sheet1.Range(Rows(5))

Set objCombinedR = Union(R1, R2)

objCombinedR.Interior.Font.Bold = True

End Sub



Also Read:

  1. VBA-Excel: Date-Time Functions – WeekDay() and WeekDayName()
  2. VBA-Excel: Array Functions – LBound and UBound()
  3. VBA-Excel: Convert Numbers (Rupees) into Words OR Text - Updated Till 1000000 Crore With Decimal Numbers
  4. Excel-VBA : Insert Multiple Images from a Folder to Excel Cells
  5. VBA-Excel: Arrays – Multi Dimensional Array
  6. VBA-Excel: Date-Time Functions – Hour(), Minute(), Second()
  7. VBA-Excel: CurrentRegion
  8. Excel-VBA : Open a Excel File using Another Excel File using Browse Option.