How to get combination (Union) of Range?

Union is a VBA function which can set multiple non-continuous ranges into a variable, For example if you want to highlight rows only with certain value in column A. you will need to use Union method. Have a look at the example here –

Sub HighLightRow()


    Dim strInput As String

    Dim rngRange As Range

    Dim rngUnion As Range

    Dim rngCell As Range


    ‘—The value you want to search in column A

    strInput = InputBox(“Please enter a value”)

     ‘—Set Range in to the variable

    Set rngRange = Range(“A1”).CurrentRegion


‘—Initiate for each look to iterate every cell in col A

    For Each rngCell In rngRange.Columns(1).Cells

      ‘—check if cell value is equal to entered value by user

        If rngCell.Value = strInput Then

           ‘—Check if rngUnion is blank

            If rngUnion Is Nothing Then

                ‘—if value in column A match with entered value

                 ‘—Set that cell row into range variable

                Set rngUnion = rngCell.EntireRow


                ‘—if there is already a range set in the variable

                 ‘—Union next matched cell with the previously set range

                Set rngUnion = Union(rngUnion, rngCell.EntireRow)

            End If

        End If

    Next rngCell


    If Not rngUnion Is Nothing Then

        rngUnion.Interior.Color = vbYellow

    End If


End Sub

Join my VBA whatsApp group to know more

Download Full Book For Free!!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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