|
VBA解法,需要把表格转换为普通区域
- Sub test()
- Dim data, i, j, dic, rng As Range, itm
- data = ThisWorkbook.Worksheets("Sheet1").UsedRange
- Set dic = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(data)
- If dic(data(i, 1) & "") = "" Then
- dic(data(i, 1) & "") = "A" & i
- Else
- dic(data(i, 1) & "") = dic(data(i, 1) & "") & "," & "A" & i
- End If
- Next i
- With ThisWorkbook.Worksheets("Sheet1")
- For Each itm In dic.items
- If UBound(Split(itm, ",")) > 0 Then
- If rng Is Nothing Then
- Set rng = .Range(itm)
- Else
- Set rng = Union(rng, .Range(itm))
- End If
- End If
- Next itm
- End With
- If Not rng Is Nothing Then rng.EntireRow.Delete
- End Sub
复制代码
PQ解法
- let
- Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
- Custom1 = Table.Group(Source,"Part Number",{{"n",each _},{"m",each Table.RowCount(_)}}),
- Custom2 = Table.Combine(Table.SelectRows(Custom1,each [m]=1)[n])
- in
- Custom2
复制代码 |
|