这应该可以帮助您:
Option Explicit
Sub ListCentreCostNames()
Dim empNamesRng As Range, ccRng As Range, ccCell As Range, cell As Range
Dim names As String
With Worksheets("centrecost") '<--| change "centercost" to your actual worksheet name
Set ccRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
Set empNamesRng = Intersect(.Columns(2), ccRng.EntireRow)
With .UsedRange
With .Resize(1, 1).Offset(, .Columns.Count + 1)
With .Resize(ccRng.Rows.Count)
.Value = ccRng.Value
.RemoveDuplicates Array(1), Header:=xlYes
For Each ccCell In .SpecialCells(xlCellTypeConstants)
ccRng.AutoFilter field:=1, Criteria1:=ccCell.Value
If Application.WorksheetFunction.Subtotal(103, ccRng) - 1 > 0 Then
names = ""
For Each cell In empNamesRng.Offset(1).Resize(empNamesRng.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
names = names & cell.Value & " "
Next cell
End If
ccCell.Offset(, 1) = names
Next ccCell
End With
End With
End With
.AutoFilterMode = False
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句