Problems with Union()

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
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:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,791
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.
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,494
Messages
5,511,654
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top