|
加了几句,搞定提取全部寝室的问题,第二个差不多
Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
Dim arr, i&, r&, d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("内务")
r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row '计算工作表最后一个非空行号
arr = .Range("A5:c" & r) '区域修改 A2:c
For i = 2 To UBound(arr)
If arr(i, 1) = "" Then
arr(i, 1) = arr(i - 1, 1)
If Not d.exists(arr(i, 1)) Then
d(arr(i, 1)) = arr(i, 3) '选择列修改arr(i, 3),的3
Else
d(arr(i, 1)) = d(arr(i, 1)) & "、" & arr(i, 3) '选择列修改arr(i, 3),的3
End If
End If
Next
End With
With Sheets("内务")
' .UsedRange.Offset(1).ClearContents
Range("ai6:ak5000").ClearContents
.[ai6].Resize(d.Count, 1) = WorksheetFunction.Transpose(d.keys)
.[aj6].Resize(d.Count, 1) = WorksheetFunction.Transpose(d.items)
End With
Set d = Nothing
Application.ScreenUpdating = True
End Sub |
|