|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub Macro1()
Dim rng As Range, brr, arr(), ary(), c As Range
Dim i As Long, lr As Long, m As Integer, temp
With Sheet1
lr = .Range("a65536").End(xlUp).Row
brr = .Range("a1:a" & lr + 1)
End With
n = 1
ReDim ary(1 To n)
ary(1) = 1
For i = 2 To lr + 1
If brr(i, 1) <> brr(i - 1, 1) Then
n = n + 1
ReDim Preserve ary(1 To n)
ary(n) = i
End If
Next
With Sheet2
.UsedRange.ClearContents
For i = 1 To n - 1
Set rng = Sheet1.Rows(ary(i) & ":" & ary(i + 1) - 1).SpecialCells(xlCellTypeConstants, 23)
ReDim arr(1 To rng.Count)
m = 0
For Each c In rng
If c.Interior.ColorIndex = xlNone Then
m = m + 1
arr(m) = c.Value
ElseIf c.Interior.ColorIndex = 3 Then
temp = c.Value
End If
Next
arr(m + 1) = temp
.Cells(i, 1).Resize(1, m + 1) = arr
Next
End With
End Sub |
|