I have a vba code I use to align and match column rows of data and up to about200 rows of data it's been working without any problems. However, I haveencountered a problem when using it on several hundred or thousands of rows of data.
Below is the code I am using to match and align the column rows of data: -
Sub RowFormat()
Dim Rng As Range
Dim Dn As Range
Dim Dic1 As Object
Set Rng = Range(Range("A2"), Range("A" &Rows.Count).End(xlUp)).Resize(, 2)
Set Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode =vbTextCompare
For Each Dn In Rng
If Not Dic1.Exists(Dn.Value) Then
Dic1.Add Dn.Value,""
Else
Dic1.Remove (Dn.Value)
End If
Next
For Each Dn In 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 Dn In Rng
If Not Dn = "" And NotDic1.Exists(Dn.Value) Then Dn.Offset(, 1) = Dn
Next Dn
EndSub
Thedata below is an example of the raw data with both columns sorted in ascendingorder 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 hasbeen 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 ofdata 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 datathereafter.
A further problem due to the miss-match in the second column, the data hasincreased by 5 items, re the duplication of V-0001 thru' V-0005, as shownbelow 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 if there is a better wayto meet my objective consistently.
Thx
Amms123
Below is the code I am using to match and align the column rows of data: -
Sub RowFormat()
Dim Rng As Range
Dim Dn As Range
Dim Dic1 As Object
Set Rng = Range(Range("A2"), Range("A" &Rows.Count).End(xlUp)).Resize(, 2)
Set Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode =vbTextCompare
For Each Dn In Rng
If Not Dic1.Exists(Dn.Value) Then
Dic1.Add Dn.Value,""
Else
Dic1.Remove (Dn.Value)
End If
Next
For Each Dn In 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 Dn In Rng
If Not Dn = "" And NotDic1.Exists(Dn.Value) Then Dn.Offset(, 1) = Dn
Next Dn
EndSub
Thedata below is an example of the raw data with both columns sorted in ascendingorder 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 hasbeen 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 ofdata 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 datathereafter.
A further problem due to the miss-match in the second column, the data hasincreased by 5 items, re the duplication of V-0001 thru' V-0005, as shownbelow 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 if there is a better wayto meet my objective consistently.
Thx
Amms123
