- Sub zz()
- Dim a, rng As Range, r&
- Application.ScreenUpdating = 0
- Columns("a:b").Insert
- Set rng = Range("a1:i" & Cells(Rows.Count, 3).End(3).Row)
- a = rng.Value
- For i = 1 To UBound(a)
- a(i, 1) = i
- If a(i, 4) <= 5 Then
- a(i, 2) = i
- a(i, 9) = a(i, 3) & "&" & a(i, 7) - 1 & "&" & a(i, 4)
- Else
- a(i, 9) = a(i, 3) & "&" & a(i, 7) & "&" & a(i, 4)
- End If
- Next
- [a1].Resize(i - 1, 9) = a
- rng.Sort key1:=rng(2), order1:=1, Header:=0
- r = Cells(Rows.Count, 2).End(3).Row
- Union(Range("g1:g" & r), Range("i1:i" & r)).Interior.Color = 65535
- Range("d1:d" & r).Interior.Color = 60000
- rng.Sort key1:=rng(1), order1:=1, Header:=0
- Columns("a:b").Delete
- Application.ScreenUpdating = 1
- End Sub
复制代码
|