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 com­bine more than one Ranges into one Range using Union Method and later on instead of writ­ing all the ranges repeat­edly, just use the com­bined one.

Exam­ple

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

Sim­i­larly you can com­bine mul­ti­ple 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 try­ing 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 cer­tain cri­te­ria as appear in my code. More­over, I am try­ing to do that with one code , presently , I can make it work but with two sep­a­rate codes when each code deal with one range only. Fur­ther­more, I would like to men­tioned that I want that code to work only in ranges A & B . thank you for your assis­tance in advance, for your ref­er­ences I included the code that work presently and the ver­sion 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 = xlCen­ter
    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 ver­sion I tried to so and not work­ing 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 under­stand­ing is you want one for loop to deal with two columns “A” and “B” at the same time. Start­ing from row 1 to use­drange . Ryt?

    I guess the below sim­ple 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 look­ing for

    Sumit

Leave a Reply

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

%d bloggers like this: