Excel VBA RemoveDuplicates not working as expected

[mcwsky09]

Not sure what I need to do to have permission to post attachments so I may have to do this in the body of the message.

I am using code such as the following:

Range("A1:B17").RemoveDuplicates Columns:=1

where the spreadsheet contains data in columns A and B like so:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]remove
[/TD]
[/TR]
</tbody>[/TABLE]

The problem is that when I run the code I end up with this:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]remove[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]remove[/TD]
[/TR]
</tbody>[/TABLE]

(the real data is more complex and has blanks instead of "remove")

But when I invoke the Remove Duplicates from the Data Menu and uncheck Column B I get what I want - like this:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]


So not sure why the VBA behavior does not match the GUI behavior.

It does seem to be an issue only with Office for Mac - I am currently using Excel for Mac 2011 version 14.4.3 (140616) and it does not have this issue with Excel for Windows 2007 version 12.0.6683.5002 SP3 MSO 12.0.6683.5000
Also tried it on Windows Office 2010 and it works as intend.

I tried it on three different Macs - all with 14.4.3 same build without success. I don't currently have any other versions loaded up to test it on.

I develop on Mac but need to maintain compatibility with both Mac and Windows versions as I have users on both.
If I cannot get this working I may have to resort to much more complex looping type code.

I have a sample workbook that I can upload (once I figure that out or reach the number of posts or whatever it takes to qualify) or can send the book to someone to look but it should not be very difficult to reproduce since it is only one line of code.

 

[hiker95]

mcwsky09,

With your raw data already sorted/grouped per you screenshot.

Sample raw data:

Excel 2007
	A	B
1	1	A
2	1	remove
3	1	remove
4	2	B
5	2	remove
6	2	remove
7	3	B
8	3	remove
9	4	C
10	4	remove
11	4	remove
12	5	D
13	5	remove
14	5	remove
15	6	E
16	6	remove
17	6	remove
18
Sheet1

After the macro:

Excel 2007
	A	B
1	1	A
2	1	remove
3	2	B
4	2	remove
5	3	B
6	3	remove
7	4	C
8	4	remove
9	5	D
10	5	remove
11	6	E
12	6	remove
13
14
15
16
17
18
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub RemoveDuplicates()
' hiker95, 07/26/2014, ME794450
Dim r As Long, lr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Range("C1:C" & lr)
  .ClearContents
  .FormulaR1C1 = "=RC[-2]&RC[-1]"
  .Value = .Value
End With
For r = 1 To lr
  n = Application.CountIf(Columns(3), Cells(r, 3).Value)
  If n > 1 Then
    Range("A" & r + 1 & ":B" & r + n - 1).ClearContents
  End If
  r = r + n - 1
Next r
Range("A1:B" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Range("C1:C" & lr).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveDuplicates macro.

 

[JLGWhiz]

In the tests that I ran for xl2007 on an old XP frame, the Excel menu driven Remove Duplicates and the VBA RemoveDuplicates gave the same results. Both delete all but the original. Looking on the web at some other posts, I see that Mac users have difficulty using the VBA version. In fact I did not see any success stories concerning a Mac using the VBA Range.RemoveDuplicates Method.

 

[alansidman]

crosspost: Excel VBA RemoveDuplicates not working as expected

 

[mcwsky09]

crosspost: Excel VBA RemoveDuplicates not working as expected

Sorry, I was unable to attach a file to the first place I posted it - and I thought that might be helpful in seeing the issue and solving it is why i posted on the other forum.

 

[hiker95]

mcwsky09,

A message to forum cross posters

Please read this:
Excelguru Help Site - A message to forum cross posters