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