|
下面这段代码是将12个乡镇数据抽取到一个工作表中,数据有一万多条,非常的慢,请老师们帮忙优化一下。- Sub 重复数据检查()
- Dim Town(12), Sh As Worksheet, s As Long, i As Byte, j As Byte, r As Long
- Town(1) = "林口镇": Town(2) = "古城镇": Town(3) = "刁翎镇": Town(4) = "五林镇": Town(5) = "朱家镇": Town(6) = "柳树镇": Town(7) = "三道通镇": Town(8) = "龙爪镇": Town(9) = "莲花镇": Town(10) = "奎山乡": Town(11) = "青山乡": Town(12) = "建堂乡" '需要抽取数据的乡镇
- Set Sh = Sheets("全县重复数据检查")
- Sh.[A1:D65536].ClearContents '清空全县重复数据检查数据
- For i = 1 To 12
- n = 0
- Sh.Cells(1, 1).Resize(, 4) = Array("姓名", "住址", "15位身份证", "是否重复") '栏位名称
- r = Sh.[A65536].End(xlUp).Row + 1 '全县重复数据检查最后空白列
- For j = 6 To 28
- If j = 6 Or j = 9 Or j = 28 Then
- n = n + 1
- s = Sheets(Town(i)).Cells(65536, j).End(xlUp).Row '各乡镇最后有数据列
- '全县重复数据检查各栏数据等于各乡镇数据
- If s > 1 Then _
- Sh.Cells(r, n).Resize(s - 1, 1) = Sheets(Town(i)).Range(Sheets(Town(i)).Cells(2, j), Sheets(Town(i)).Cells(s, j)).Value
- End If
- Next j
- Next i
- End Sub
复制代码 |
|