|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
回复 18楼 vincent.cn 的帖子
请测试:
Sub Macro1()
Dim rng As Range, brr, crr, arr(), ary(), drr(), c As Range
Dim i As Long, lr As Long, lc As Integer, m As Integer, ii As Integer, j As Integer
With Sheet1
lr = .Range("a65536").End(xlUp).Row
lc = .UsedRange.Columns.Count
brr = .Range("a1").Resize(lr + 1, lc)
crr = .UsedRange
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
ReDim drr(1 To n - 1)
For i = 1 To lr
If i > 1 Then
If brr(i, 1) = brr(i - 1, 1) Then brr(i, 1) = ""
End If
If Right(brr(i, 2), 2) <> "10" Then
m = m + 1
drr(m) = brr(i, 4)
For j = 4 To 7
brr(i, j) = ""
Next
End If
For j = 8 To lc
If InStr(brr(i, j), "'") Then brr(i, j) = ""
Next
brr(i, 2) = ""
Next
Application.ScreenUpdating = False
With Sheet1
.Range("a1").Resize(lr + 1, lc) = brr
Sheet2.UsedRange.ClearContents
For i = 1 To n - 1
brr = .Cells(ary(i), 1).Resize(ary(i + 1) - ary(i), lc)
m = 0
For ii = 1 To UBound(brr)
For j = 1 To lc
If brr(ii, j) <> "" Then
m = m + 1
ReDim Preserve arr(1 To m)
arr(m) = brr(ii, j)
End If
Next
Next
m = m + 1
ReDim Preserve arr(1 To m)
arr(m) = drr(i)
Sheet2.Cells(i, 1).Resize(1, m) = arr
Erase arr
Next
.UsedRange.ClearContents
.Cells.Resize(lr, lc) = crr
End With
Application.ScreenUpdating = True
End Sub |
|