Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

 

 

You may also like...

3 Responses

  1. Mike MS says:

    Hi to all
    I am trying to make this macro work, I would like to go through range A & B ,(all the way from A1,b1 to the last used row or from last row up (end(xlup), and check for the certain criteria as appear in my code. Moreover, I am trying to do that with one code , presently , I can make it work but with two separate codes when each code deal with one range only. Furthermore, I would like to mentioned that I want that code to work only in ranges A & B . thank you for your assistance in advance, for your references I included the code that work presently and the version of the code I tried to make which is not working

    Sub test_14() ‘ this is the code that work but only for one range
    Application.ScreenUpdating = False
    Dim r As range
    For Each r In range(“A1”, range(“A” & Rows.Count).End(xlUp))
    If r.Value Like “*[A-z]*” Then
    r.HorizontalAlignment = xlCenter
    ElseIf r.NumberFormat = “0.0%,-0.0%” Then
    r.HorizontalAlignment = xlRight
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    Sub test_4() ‘ this is the version I tried to so and not working for me
    Application.ScreenUpdating = False
    Dim r As range
    r = range(“A1:B1”, range(“A:B” & Rows.Count).End(xlUp))
    For Each Ranges In range(“r1”, range(“r” & Rows.Count).End(xlUp))
    If r.Value Like “*[A-z]*” Then
    r.HorizontalAlignment = xlCenter

    ElseIf r.NumberFormat = “0.0%,-0.0%” Then
    r.HorizontalAlignment = xlRight
    End If

    Next
    Application.ScreenUpdating = True
    End Sub

  2. Sumit Jain says:

    My understanding is you want one for loop to deal with two columns “A” and “B” at the same time. Starting from row 1 to usedrange . Ryt?

    I guess the below simple code will do this job–

    Sub sumit()

    Dim mainWB As Workbook

    Set mainWB = ActiveWorkbook

    rw = mainWB.Sheets(“Sheet1”).UsedRange.Rows.Count
    For i = 1 To rw
    mainWB.Sheets(“Sheet1”).Range(“A” & i) = i
    mainWB.Sheets(“Sheet1”).Range(“B” & i) = i
    Next
    End Sub

    Please let me know you this is not hat u were looking for

    Sumit

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: