Problems with Union()

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,679
Hey All,

I have all but broken my head over this. And I can't seem to find where the problem is!!!

This is NewRng
Excel Workbook
A
12LUNDBURG, MALIN ISABELLE
13ODIFE, IKEMEFUNA
14RAM, ROORH
15SINGH, AMAR
16ABDOLAZIN REZAIE POOR BASHIR
17ABDUL MOMIN, ABDUL
18ABDUL RAZZAQ, PULIA MANDAN
19ABDUL, SALEEM
Data
Excel 2003

This is IniRng
Excel Workbook
A
8LUNDBURG, MALIN
9ODIFE, IKEMEFUNA
10RAM, ROORH
11SINGH, AMAR
12ABDOLAZIN REZAIE BASHIR
13ABDUL MOMIN, ABDUL
14ABDUL RAZZAQ, PULIA MANDAN
15ABDUL, SALEEM
Historical
Excel 2003

I am trying to compare IniRng with NewRng to see if there are any changes. As can be seen, there are 2 changes (the 1st name and the 5th name)

The code below detects both changes but RemRng is populated with only the 1st change. Can anyone see where I am going wrong? I've tried Union and Application.Union but get same results.<font face=Courier New>            <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rCell<SPAN style="color:#00007F">In</SPAN> IniRng<br>                <SPAN style="color:#00007F">If</SPAN> IsError(WorksheetFunction.Match(rCell.Value, NewRng, 0))<SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> RemRng<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><br>                        <SPAN style="color:#00007F">Set</SPAN> RemRng = rCell<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        <SPAN style="color:#00007F">Set</SPAN> RemRng = Union(RemRng, rCell)<br>                    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> rCell<br>            RemCount = RemRng.Rows.Count</FONT>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
The Union Command apparently does not give a 'regular' Range as a result.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I found that this line will not run without giving an Error in Excel 2003:<o:p></o:p>
If IsError(WorksheetFunction.Match(rcell.Value, [A6:A8], 0)) Then<o:p></o:p>
<o:p></o:p>
An array can be used instead of<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on">Union</st1:place>.<o:p></o:p>
So instead the results can be added to an array one by one:
Rich (BB code):
Rich (BB code):
Rich (BB code):
Sub Test1()
Dim RemRng As Range
Dim sNewRng() As String
Dim x As Long
x = 1
ReDim sNewRng(1 To 1)
On Error GoTo ERR0R1
For Each rCell In [A2:A4]
    IsError (WorksheetFunction.Match(rCell.Value, [A6:A8], 0))
Next rCell
Debug.Print "RemRng.Rows.Count"
Debug.Print RemRng.Rows.Count
Debug.Print "UBound(sNewRng)"
Debug.Print UBound(sNewRng)
[A11:A12] = Application.WorksheetFunction.Transpose(sNewRng)
RemRng.Font.ColorIndex = 3
RemRng.Font.Bold = True
Exit Sub
ERR0R1:
If RemRng Is Nothing Then
    Set RemRng = rCell
    sNewRng(1) = rCell
Else
    Set RemRng = Union(RemRng, rCell)
    x = x + 1
    ReDim Preserve sNewRng(1 To x)
    sNewRng(x) = rCell
End If
Resume Next
End Sub

Font is
Bold Red after running sub.<o:p></o:p>
Excel Workbook
A
2A A
3B
4C
5
6A
7B
8C D
9
10Result
11A A
12C
...
<o:p></o:p>

Result of Debug.Print:<o:p></o:p>
<o:p></o:p>
RemRng.Rows.Count
1
UBound(sNewRng)
2<o:p></o:p>

<o:p></o:p>
For non-matches in adjacent Rows, the Row Count = 2<o:p></o:p>
Excel Workbook
A
2A A
3B C
4C
5
6A
7B
8C
9
10Result
11A A
12B C
...
<o:p></o:p>

Result of Debug.Print:<o:p></o:p>
RemRng.Rows.Count
2
UBound(sNewRng)
2

 
Last edited:
Upvote 0
RemRng is a discontinous range. Instead of testing rows, you should be using .Cells.Count.

To work RemRng further you will have to work with its Areas, rather than the whole range.
 
Upvote 0
Hi Sandeep

RemRng is a multi-area range. If you do not specify the area then RemRng.Rows.Count gives you the count of rows in the first area.

The Union Command apparently does not give a 'regular' Range as a result.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

Hi Jack

Yes it does. It just happens that in this case the cells were not contiguous.
 
Upvote 0
Hi All,

Thanks for your responses.

I was under the impression that even though the relevant values in IniRng are discontinuous, since I was populating them in a fresh range, they would be continuous in it. Looks like I was wrong.

I was planning on a
Code:
Range("A1").Resize(RemRng.Rows.Count).Value = RemRng

I went with the array method and used UBound() to get counts. To populate a range on the sheet I used Transpose(Array). Seems to be working for now.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top