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 *