|
帮忙优化下,数据比较大,其实就是发板SHEET内的SN不能和别的SHEET内B列内容一致。
Sub a()
Dim w As Worksheet, p()
Set w = Worksheets("发板")
r = w.Cells(Rows.Count, 2).End(xlUp).Row
w.Range("d2" & ":" & "d" & r) = "=COUNTIFS(报废!B:B,B:B)"
w.Range("e2" & ":" & "e" & r) = "=COUNTIFS(待修!B:B,B:B)"
w.Range("f2" & ":" & "f" & r) = "=COUNTIFS(出板!B:B,B:B)"
w.Range("a1" & ":" & "f" & r).Copy
w.Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
s = w.Range("a2" & ":" & "f" & r)
i = 2
For s = r To 2 Step -1
e = w.Cells(s, 4) & w.Cells(s, 5) & w.Cells(s, 6)
If e = 0 Then
Else
Rows(s).Delete
End If
Next s
w.Range("d:f") = ""
End Sub
|
|