Row match and alignment VBA code

[Amms123]

I have a vba code I use to align and match column rows ofdata and up to about 200 rows of data it's been workingwithout any problems. However, I have encountered a problem when using it on severalhundred or thousands of rows of data.

Below is the code I am using to match and align the column rows of data: -


SubRowFormat()
Dim Rng AsRange
Dim Dn AsRange
Dim Dic1 AsObject
Set Rng =Range(Range("A2"), Range("A" &Rows.Count).End(xlUp)).Resize(, 2)
Set Dic1 =CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
For Each DnIn Rng
If Not Dic1.Exists(Dn.Value) Then
Dic1.Add Dn.Value, ""
Else
Dic1.Remove (Dn.Value)
End If
Next

For Each DnIn Rng
If Dn <> "" AndDic1.Exists(Dn.Value) Then
If Dn.Column = 1 Then
Dn.Offset(, 1).Insert
Else
Dn.Offset(, -1).Insert
End If
End If
Next Dn
Set Rng =Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each DnIn Rng
If Not Dn = "" And NotDic1.Exists(Dn.Value) Then Dn.Offset(, 1) = Dn
Next Dn
End Sub

T

he data below is an example of the raw data with both columns sorted in ascending order before running the code.​

A-0001-T-01

A-0002-T-02
A-0002-V-01 A-0002-V-02
AH-01-V-0001 AX-00001
B-01-RG-01​

C-0002
E-00001A E-00002A
E-00002A

Below is the data after running the code, as you can see the second column has been aligned to match the first column and visa versa.
If there is no match it leaves a blank cell and goes onto the next row of data

A-0001-T-01
A-0002-T-02
A-0002-V-01
A-0002-V-02
AH-01-V-0001
AX-00001
B-01-RG-01
C-0002
E-00001A
E-00002A

E-00002A

However, a problem has occurred with the code when used on bigger amounts of data and below shows the match and alignment has failed for the rows 458 thru' 462, It then continues to correctly match and align the rows of data thereafter.

A further problem due to the miss-match in the second column is, the data has increased by 5 items, re the duplication of V-0001 thru' V-0005, as shown below in red font.


US-00-154
US-00-155 ​

​

V-0001​

​

US-00-156 ​

​

​

​

​

​

​

​

V-0002​

​

​

​

​

US-00-157 ​

​

​

V-0003​

​

​

US-00-158 ​

​

​

​

​

V-0004​

​

​

​

​

US-00-162 ​

​

​

V-0005​

​

​

V-0001 V-0001
V-0002 V-0002
V-0003 V-0003
V-0004 V-0004
V-0005 V-0005
V-0006 V-0006
V-0007 V-0007​

X-0001
X-0002

I would be grateful for any advice how to remedy this problem, or a better way to meet my objective consistently.

Thx

Amms123




​

​

<strike></strike>


<strike></strike>

<strike></strike>

 

[Amms123]

Yes, it works well, now I know what the error was related too. Thx again

 

[DanteAmor]

Youre welcome.