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.