On Sun, 15 Jan 2012 07:44:41 -0800 (PST), gary wrote:

Hi Ron,

My spreadsheet has:

A B
0000000021957 0000000022002
0000000022002 0000000032002
0000000031957 0000000032003
0000000032002 0000000042002
0000000032003 0000000052002
0000000042002 0000000052003
0000000052002 0000000062002
0000000052003 0000000102002
0000000061967 0000000121996
0000000061968 0000000142002
0000000062002 0000000152002
0000000081963 0000000162002

Your macro (without Transpose) returns this:

0000000021957
22002
31957
61967
61968
81963
341991
401961
431978
482010
482011

In my spreadsheet:
A2 contains 0000000022002
B1 contains 0000000022002

But your macro results contains 22002
Why are the leading zeroes being dropped?

I'm glad to hear that the macro is working and not taking hours :-)
The leading zero's are being dropped because Excel is trying to be helpful and interpreting the data as numeric. We have two choices to change this and retain the speed:
We can format the column as text.
We can custom format the column to "0000000000000" (thirteen zero's)
The latter retains the numeric characteristics; the former does not, but some Excel functions will still interpret this as a number. The choice is yours.

Here's how to modify the code to provide for that. Note the lines near the bottom.

===================================
Option Explicit
Sub PruneColA()
'Requires setting reference (tools/references) to
' Microsoft Scripting Runtime

Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim dColA As Dictionary, dColB As Dictionary
Dim i As Long
Dim d As Variant

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
With dColB
If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i

For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)
If Not dColB.Exists(Key:=vColA(i, 1)) Then
With dColA
If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
End With
End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
i = i + 1
vColA(i, 1) = dColA(d)
Next d

rColA.Offset(rowoffset:=1).ClearContents
Set rColA = rColA.Resize(rowsize:=dColA.Count).Offset(rowoffse t:=1)

'UNcomment one or the other of the next two lines depending on your preference
'rColA.EntireColumn.NumberFormat = "0000000000000"
rColA.EntireColumn.NumberFormat = "@"

rColA = vColA

End Sub
=======================================

Great work Ron

Thanks for coming to the rescue.


Gord

On Sun, 15 Jan 2012 14:03:40 -0500, Ron Rosenfeld
wrote:

I'm glad to hear that the macro is working and not taking hours :-)
The leading zero's are being dropped because Excel is trying to be helpful and interpreting the data as numeric. We have two choices to change this and retain the speed:
We can format the column as text.
We can custom format the column to "0000000000000" (thirteen zero's)
The latter retains the numeric characteristics; the former does not, but some Excel functions will still interpret this as a number. The choice is yours.

Here's how to modify the code to provide for that. Note the lines near the bottom.

===================================
Option Explicit
Sub PruneColA()
'Requires setting reference (tools/references) to
' Microsoft Scripting Runtime

Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim dColA As Dictionary, dColB As Dictionary
Dim i As Long
Dim d As Variant

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
With dColB
If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i

For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)
If Not dColB.Exists(Key:=vColA(i, 1)) Then
With dColA
If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
End With
End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
i = i + 1
vColA(i, 1) = dColA(d)
Next d

rColA.Offset(rowoffset:=1).ClearContents
Set rColA = rColA.Resize(rowsize:=dColA.Count).Offset(rowoffse t:=1)

'UNcomment one or the other of the next two lines depending on your preference
'rColA.EntireColumn.NumberFormat = "0000000000000"
rColA.EntireColumn.NumberFormat = "@"

rColA = vColA

End Sub
=======================================

On Sun, 15 Jan 2012 11:08:54 -0800, Gord Dibben wrote:

Great work Ron

Thanks for coming to the rescue.


Gord

It was an interesting problem. When I started off, I thought Advanced Filter would be the way to go. I was surprised when it was so problematic dealing with very large sets of data. But then I did some research and reminded myself that others had reported problems in the past with 2007 and the Advanced Filter. Of coursae, then I ran into limitations in using VBA arrays and the Transpose worksheetfunction. But in developing this solution, I learned a lot. I think I could have also used Collections, and avoided the reference to the Scripting Runtime package. I don't know which would be faster. Clearly, the Exist property of a dictionary takes fewer steps than error processing for a collection, in order to tell if something exists; but you have to execute the Exist function each time.

On Sun, 15 Jan 2012 11:08:54 -0800, Gord Dibben wrote:

Great work Ron

Thanks for coming to the rescue.


Gord

And I was really pleasantly surprised at how quickly the dictionary method worked.

On Jan 15, 11:03*am, Ron Rosenfeld wrote:
On Sun, 15 Jan 2012 07:44:41 -0800 (PST), gary wrote:
Hi Ron,

My spreadsheet has:

* * * * * A * * * * * * * * * * * * * * * *B
0000000021957 * * * 0000000022002
0000000022002 * * * 0000000032002
0000000031957 * * * 0000000032003
0000000032002 * * * 0000000042002
0000000032003 * * * 0000000052002
0000000042002 * * * 0000000052003
0000000052002 * * * 0000000062002
0000000052003 * * * 0000000102002
0000000061967 * * * 0000000121996
0000000061968 * * * 0000000142002
0000000062002 * * * 0000000152002
0000000081963 * * * 0000000162002

Your macro (without Transpose) returns this:

0000000021957
22002
31957
61967
61968
81963
341991
401961
431978
482010
482011

In my spreadsheet:
A2 contains 0000000022002
B1 contains 0000000022002

But your macro results contains 22002
Why are the leading zeroes being dropped?

I'm glad to hear that the macro is working and not taking hours :-)
The leading zero's are being dropped because Excel is trying to be helpful and interpreting the data as numeric. *We have two choices to change this and retain the speed:
* We can format the column as text.
* We can custom format the column to "0000000000000" *(thirteen zero's)
The latter retains the numeric characteristics; the former does not, but some Excel functions will still interpret this as a number. *The choice is yours.

Here's how to modify the code to provide for that. *Note the lines near the bottom.

===================================
Option Explicit
Sub PruneColA()
'Requires setting reference (tools/references) to
' *Microsoft Scripting Runtime

* * Dim ws As Worksheet
* * Dim rColA As Range, rColB As Range
* * Dim vColA As Variant, vColB As Variant
* * Dim dColA As Dictionary, dColB As Dictionary
* * Dim i As Long
* * Dim d As Variant

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
* * Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
* * Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
* * With dColB
* * * * If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
* * End With
Next i

For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)
* * If Not dColB.Exists(Key:=vColA(i, 1)) Then
* * * * With dColA
* * * * * * If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
* * * * End With
* * End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
* * i = i + 1
* * vColA(i, 1) = dColA(d)
Next d

rColA.Offset(rowoffset:=1).ClearContents
Set rColA = rColA.Resize(rowsize:=dColA.Count).Offset(rowoffse t:=1)

'UNcomment one or the other of the next two lines depending on your preference
'rColA.EntireColumn.NumberFormat = "0000000000000"
rColA.EntireColumn.NumberFormat = "@"

rColA = vColA

End Sub
=======================================- Hide quoted text -

- Show quoted text -

I'm using:

rColA.EntireColumn.NumberFormat = "0000000000000"
'rColA.EntireColumn.NumberFormat = "@"

Because the result still contains 0000000022002 (which is in Col B)
and this makes the result suspect.

On Sun, 15 Jan 2012 12:28:24 -0800 (PST), gary wrote:

I'm using:

rColA.EntireColumn.NumberFormat = "0000000000000"
'rColA.EntireColumn.NumberFormat = "@"

Because the result still contains 0000000022002 (which is in Col B)
and this makes the result suspect.

Well, if you need column A to be numeric, then column B must be numeric also. If column B values are text, then you should use the Text format "@".

When I was testing, I had preformatted both columns as text, and had no problems.

Also, please note that I assumed you would have some label in Row 1. If there are no labels, then try this variation, which should work whether or not there is a label:

===============================
Option Explicit
Sub PruneColA()
'Requires setting reference (tools/references) to
' Microsoft Scripting Runtime

Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim dColA As Dictionary, dColB As Dictionary
Dim i As Long
Dim d As Variant

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) To UBound(vColB, 1)
With dColB
If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i

For i = LBound(vColA, 1) To UBound(vColA, 1)
If Not dColB.Exists(Key:=vColA(i, 1)) Then
With dColA
If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
End With
End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
i = i + 1
vColA(i, 1) = dColA(d)
Next d

rColA.Offset.ClearContents
rColA.EntireColumn.NumberFormat = "@"
Set rColA = rColA.Resize(rowsize:=dColA.Count)

rColA = vColA
End Sub
=================================================

On Sun, 15 Jan 2012 20:23:36 -0500, Ron Rosenfeld wrote:

SEE BELOW FOR OOPS.



On Sun, 15 Jan 2012 12:28:24 -0800 (PST), gary wrote:

I'm using:

rColA.EntireColumn.NumberFormat = "0000000000000"
'rColA.EntireColumn.NumberFormat = "@"

Because the result still contains 0000000022002 (which is in Col B)
and this makes the result suspect.

Well, if you need column A to be numeric, then column B must be numeric also. If column B values are text, then you should use the Text format "@".

When I was testing, I had preformatted both columns as text, and had no problems.

Also, please note that I assumed you would have some label in Row 1. If there are no labels, then try this variation, which should work whether or not there is a label:

===============================
Option Explicit
Sub PruneColA()
'Requires setting reference (tools/references) to
' Microsoft Scripting Runtime

Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim dColA As Dictionary, dColB As Dictionary
Dim i As Long
Dim d As Variant

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) To UBound(vColB, 1)
With dColB
If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i

For i = LBound(vColA, 1) To UBound(vColA, 1)
If Not dColB.Exists(Key:=vColA(i, 1)) Then
With dColA
If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
End With
End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
i = i + 1
vColA(i, 1) = dColA(d)
Next d

rColA.Offset.ClearContents
rColA.EntireColumn.NumberFormat = "@"
Set rColA = rColA.Resize(rowsize:=dColA.Count)

rColA = vColA
End Sub
=============================================== ==

OOPS:

rColA.Offset.ClearContents

should read:

rColA.EntireColumn.ClearContents

Ron,
For what it is worth...
Since, a variant filled with an array of Range.Values is one based and a Dictionary object is one
based,
the two code lines below should probably omit the "+ 1".

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)






"Ron Rosenfeld"
wrote in message
...
I'm glad to hear that the macro is working and not taking hours :-)
The leading zero's are being dropped because Excel is trying to be helpful and interpreting the
data as numeric. We have two choices to change this and retain the speed:
We can format the column as text.
We can custom format the column to "0000000000000" (thirteen zero's)
The latter retains the numeric characteristics; the former does not, but some Excel functions will
still interpret this as a number. The choice is yours.

Here's how to modify the code to provide for that. Note the lines near the bottom.

===================================
Option Explicit
Sub PruneColA()
'Requires setting reference (tools/references) to
' Microsoft Scripting Runtime

Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim dColA As Dictionary, dColB As Dictionary
Dim i As Long
Dim d As Variant

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
With dColB
If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i

For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)
If Not dColB.Exists(Key:=vColA(i, 1)) Then
With dColA
If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
End With
End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
i = i + 1
vColA(i, 1) = dColA(d)
Next d

rColA.Offset(rowoffset:=1).ClearContents
Set rColA = rColA.Resize(rowsize:=dColA.Count).Offset(rowoffse t:=1)

'UNcomment one or the other of the next two lines depending on your preference
'rColA.EntireColumn.NumberFormat = "0000000000000"
rColA.EntireColumn.NumberFormat = "@"

rColA = vColA

End Sub
=======================================

From the responses and their results, I think it'd be best to re-state
my OP:

I need a list of the values in Col A that are NOT found in Col B.

My spreadsheet contains:

A B
0000000021957 0000000022002
0000000022002 0000000032002
0000000031957 0000000032003
0000000032002 0000000042002
0000000032003 0000000052002
0000000042002 0000000052003
0000000052002 0000000062002
0000000052003 0000000102002
0000000061967 0000000121996
0000000061968 0000000142002
0000000062002 0000000152002
0000000081963 0000000162002
0000000102002 0000000481994
0000000121996 0000000481995
0000000142002 0000000481996
0000000152002 0000000481997
0000000162002 0000000481998
0000000341991 0000000481999
0000000401961 0000000482000
0000000431978 0000000482002
0000000481994 0000000482008
0000000481995 0000000482009
0000000481996 0000000631995
0000000481997 0000000631996
0000000481998 0000000631997
0000000481999 0000000631998
0000000482000 0000000631999
0000000482002 0000000632000
0000000482008 0000000632001
0000000482009 0000000642000
0000000482010 0000000681994
0000000482011 0000000681995
0000000491959 0000000681996
0000000511958 0000000681997
0000000591982 0000000681998
0000000591983 0000000911997
0000000591984 0000000962001
0000000591990 0000000962003
0000000591991 0000001001997
0000000611962 0000001082006
0000000631993 0000001381994
0000000631995 0000001381995
0000000631996 0000002001994
0000000631997 0000002122007
0000000631998 0000002291995
0000000631999 0000002291996
0000000632000 0000002291997
0000000632001 0000002601999
0000000641984 0000002602000
0000000642000 0000002641998
0000000661957 0000002731994
0000000681994 0000003031994
0000000681995 0000003161994
0000000681996 0000003161995
0000000681997 0000003161996
0000000681998 0000003161997
0000000691959 0000003392009
0000000751990 0000003901998
0000000811961 0000004062006
0000000811991 0000004091994
0000000811992 0000004091995
0000000811993 0000004131998
0000000821959 0000004231998
0000000851958 0000004371995
0000000881990 0000004521995
0000000911997 0000004522000
0000000951959 0000004541997
0000000962001 0000004542000
0000000962003 0000004542001
0000001001997 0000005001998
0000001031957 0000005002002
0000001082006 0000005121997
0000001121970 0000005181994
0000001121973 0000005181998
0000001121974 0000005381995
0000001121975 0000005381996
0000001121976 0000005471994
0000001181960 0000005471995
0000001191952 0000005471996
0000001311961 0000005581996
0000001341959 0000005622000
0000001381994 0000005622001
0000001381995 0000005622002
0000001411981 0000005971994
0000001411982 0000006202004
0000001411983 0000006491995
0000001411991 0000006511994
0000001411992 0000006511996
0000001421956 0000006571994
0000001451982 0000006571995
0000001471982 0000006571996
0000001541990 0000007291994
0000001561957 0000007291996
0000001631971 0000007291998
0000001631972 0000007321994
0000001681959 0000007341997
0000001711991 0000007341998
0000001781953 0000007432003
0000001871955 0000007751995
0000001881952 0000007931996
0000001881955 0000008071994
0000001881959 0000008071995
0000001921986 0000008071997
0000001951958 0000008432000
0000001981958 0000008432001
0000002001954 0000008432002
0000002001955 0000008631995
0000002001994 0000008631996
0000002011957 0000008662000
0000002031958 0000008681998
0000002101957 0000008702003
0000002122007 0000008702004
0000002191957 0000008702005
0000002191963 0000008702006
0000002211979 0000008771994
0000002211980 0000008771995
0000002241960 0000008771996
0000002251958 0000008771997
0000002271976 0000008771998
0000002281981 0000008772000
0000002281982 0000008772003
0000002291982 0000008961994
0000002291983 0000008991996
0000002291984 0000008992005
0000002291985 0000008992006
0000002291986 0000009061997
0000002291987 0000009061998
0000002291993 0000009062006
0000002291995 0000009062007
0000002291996 0000009062009
0000002291997 0000009171995
0000002331955 0000009171997
0000002331961 0000009172003
0000002381990 0000009221994
0000002391960 0000009221996
0000002411958 0000009361995
0000002411967 0000009362003
0000002421958 0000009362004
0000002461981 0000009401994
0000002461982 0000009581998
0000002461983 0000009691994
0000002531986 0000009691995
0000002571990 0000009691996
0000002571991 0000009821996
0000002591977 0000009831996
0000002601963 0000009831997
0000002601999 0000009971994
0000002602000 0000009971995
0000002641998 0000009971996
0000002691990 0000009971997
0000002691991 0000009971998
0000002711961 0000009971999
0000002731990 0000009972000
0000002731992 0000009972001
0000002731993 0000009972002
0000002731994 0000009972003
0000002741956 0000009972004
0000002741990 0000009972005
0000002871977 0000009972006
0000002891956 0000010001998
0000002921961 0000010011994
0000002971956 0000010011995
0000002971991 0000010321994
0000002981959 0000010361997
0000003001989 0000010411996
0000003001990 0000010411997
0000003021990 0000010411998
0000003031994 0000010411999

On Sun, 15 Jan 2012 14:28:31 -0800 (PST), gary wrote:

From the responses and their results, I think it'd be best to re-state
my OP:

I need a list of the values in Col A that are NOT found in Col B.

Just use the same routine, but instead of clearing Col A and then writing the results back to Col A, define rDest and write the results the

=============================
Option Explicit
Sub SelectFromColA()
'Requires setting reference (tools/references) to
' Microsoft Scripting Runtime

Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim dColA As Dictionary, dColB As Dictionary
Dim i As Long
Dim d As Variant
Dim rDest As Range

Set dColA = New Dictionary
Set dColB = New Dictionary
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
Set rDest = .Cells(1, 5)
End With

vColB = rColB
vColA = rColA

For i = LBound(vColB, 1) To UBound(vColB, 1)
With dColB
If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i

For i = LBound(vColA, 1) To UBound(vColA, 1)
If Not dColB.Exists(Key:=vColA(i, 1)) Then
With dColA
If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1)
End With
End If
Next i

ReDim vColA(1 To dColA.Count, 1 To 1)
i = 0
For Each d In dColA
i = i + 1
vColA(i, 1) = dColA(d)
Next d

rDest.EntireColumn.ClearContents
rDest.EntireColumn.NumberFormat = "@"
Set rDest = rDest.Resize(rowsize:=dColA.Count)

rDest = vColA
End Sub
==========================

On Sun, 15 Jan 2012 14:28:31 -0800 (PST), gary wrote:

From the responses and their results, I think it'd be best to re-state
my OP:

I need a list of the values in Col A that are NOT found in Col B.

My spreadsheet contains:

A B
0000000021957 0000000022002
0000000022002 0000000032002

Gary,

Hopefully you've got the formatting issue sorted.
While you've been away, Jim, GS and I have been doing further work on this method. Here is a routine that also provides a list of unique (no duplicates) items in Column A that are not found in Column B, and it runs in 1/6 the time of my last macro. If the previous took a minute to run on your data set, I expect this one will run in about 10 seconds. Note that it does NOT require a reference to Microsoft Scripting Runtime

================================
Option Explicit
Sub PruneColA2()
Dim ws As Worksheet
Dim rColA As Range, rColB As Range
Dim vColA As Variant, vColB As Variant
Dim vResults As Variant
Dim cColB As Collection
Dim i As Long
Dim lBlanks As Long
Dim v As Variant
Dim rDest As Range

Set cColB = New Collection
Set ws = ActiveSheet
With ws
Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
Set rDest = .Cells(1, 5) 'sets column for the results
End With

vColB = rColB
vColA = rColA

On Error Resume Next
For i = LBound(vColB, 1) To UBound(vColB, 1)
With cColB
.Add Key:=vColB(i, 1), Item:=vColB(i, 1)
End With
Next i
On Error GoTo 0

On Error GoTo NotUniqueItem
For i = LBound(vColA, 1) To UBound(vColA, 1)
cColB.Add Item:=vColA(i, 1), Key:=vColA(i, 1)
Next i

ReDim vResults(1 To UBound(vColA) - lBlanks, 1 To 1)
i = 0
For Each v In vColA
If v < "" Then
i = i + 1
vResults(i, 1) = v
End If
Next v


rDest.EntireColumn.ClearContents
rDest.EntireColumn.NumberFormat = "@"

Set rDest = rDest.Resize(rowsize:=UBound(vResults, 1))

rDest = vResults

Exit Sub

NotUniqueItem:
vColA(i, 1) = ""
lBlanks = lBlanks + 1
Resume Next
End Sub
==================================

Ron,
Actually, I misspoke, the Dictionary is zero based, but the recommendation stands.
Also, the two lines near the end should read...
rColA.ClearContents
Set rColA = rColA.Resize(dColA.Count, 1)
'---
Jim Cone


"Jim Cone"
wrote in message
...
Ron,
For what it is worth...
Since, a variant filled with an array of Range.Values is one based and a Dictionary object is one
based,
the two code lines below should probably omit the "+ 1".

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)

On Sun, 15 Jan 2012 16:47:41 -0800, "Jim Cone" wrote:

Ron,
Actually, I misspoke, the Dictionary is zero based, but the recommendation stands.
Also, the two lines near the end should read...
rColA.ClearContents
Set rColA = rColA.Resize(dColA.Count, 1)
'---
Jim Cone

That is if you remove the +1. As I wrote, when that was written I had assumed a column label. If the column label is unique, and not found in Column B, then it can be included as I did in a subsequent response, following your suggestion, to the OP.

On Sun, 15 Jan 2012 13:31:13 -0800, "Jim Cone" wrote:

Ron,
For what it is worth...
Since, a variant filled with an array of Range.Values is one based and a Dictionary object is one
based,
the two code lines below should probably omit the "+ 1".

For i = LBound(vColB, 1) + 1 To UBound(vColB, 1)
For i = LBound(vColA, 1) + 1 To UBound(vColA, 1)

Jim,
I assumed that row 1 had a label that did not need to be included in the comparison. If there are no column labels, or if there are but it is guaranteed that they won't be repeated in the data, then you are correct.

-- Ron