实例需求:在如下图的数据记录表中标识重复数据。
- 数据由D列开始,总列数不固定
- 按数据块(包含三行)进行比对,例如D4:D6为一个数据块
- 只在同一列中进行数据对比,数据块D4:D6与数据块D13:D15重复,两个数据块都标黄
- 忽略全部为空的数据块
示例代码如下:
Sub Demo()
Dim rngRes As Range, rngData
Dim lstc, lstr, arr, c, r, k, r1, cur, res, rngkey, rnglook
lstc = Cells(3, Columns.Count).End(xlToLeft).Column
lstr = Cells(Rows.Count, "C").End(xlUp).row
Set rngData = Range([D4], Cells(lstr, lstc))
rngData.Interior.Pattern = xlNone
arr = rngData.Value
For c = 1 To UBound(arr, 2)
For r = 1 To UBound(arr, 1) - 1 Step 3
res = ""
For k = 0 To 2
res = res & "|" & Round(arr(r + k, c), 3)
Next
If res <> "|0|0|0" Then
For r1 = r + 3 To UBound(arr, 1) Step 3
cur = ""
For k = 0 To 2
cur = cur & "|" & Round(arr(r1 + k, c), 3)
Next
If res = cur Then
Set rngkey = Cells(r + 3, c + 3).Resize(3, 1)
Set rnglook = Cells(r1 + 3, c + 3).Resize(3, 1)
If rngRes Is Nothing Then
Set rngRes = rngkey
Else
Set rngRes = Union(rngRes, rngkey)
End If
Set rngRes = Union(rngRes, rnglook)
End If
Next
End If
Next
Next
If Not rngRes Is Nothing Then rngRes.Interior.Color = vbYellow
End Sub
【代码解析】
数据对比肯定就需要使用多重循环。
第3行代码获取数据表的最大列数。
第4行代码获取数据表的最大行数。
第5行代码保存数据所在单元格区域。
第7行代码清空数据区域的填充色。
第8行代码将数据区域加载到数组中,以加快代码处理速度。
第9行代码按列进行循环。
第10行代码按行进行循环,步进间隔为3,即一个数据块包含3个单元格。
第12到14行代码将数据块(下称为KEY数据块)中的数据合并为一个字符串,间隔符为竖线。
第15行判断字符块是否为空,注意第8行代码加载数组时,对于空单元格,其数组值为0,而不是空。
第16行从KEY数据块之下的当前列中循环比对。
第18到20行代码使用相同方式将被比对数据块合并为字符串。
第21号判断两个数据块是否完全相同。
如果两个数据块内容重复,那么第22到29行代码将相关数据块单元格区域合并到rngRes对象变量(重复数据所在单元格区域)中。
第22和23行代码分别获取KEY数据块和比对数据块的单元格区域。
第24到28行代码将rngkey合并到rngRes中。
第29行代码将rnglook合并到rngRes中。
第35行代码将重复数据所在单元格区域的填充色设置为黄色。