如果此子在 A 列中找到匹配值，它將這兩個值合并為一行，然后將列 B 和 C 相加到運行行。
我一直在嘗試更改它，以便發生這種情況，它需要在 A 和 B 列中找到匹配項，然后將 C 和 D 相加。
例如：
A A 5 5
A A 5 5
A B 6 1
會變成
A A 10 10
A B 6 1
Sub Consolidate()
Application.ScreenUpdating = False
Dim s As Worksheet, last_row As Long
Dim row As Long
Dim col As Integer, v, m
Set s = Worksheets("Sheet12")
s.Activate
last_row = s.Cells(s.rows.Count, 1).End(xlUp).row 'find the last row with data
For row = last_row To 3 Step -1
v = s.Cells(row, "A").Value
m = Application.Match(v, s.Columns("A"), 0) 'find first match to this row
If m < row Then 'earlier row?
'combine rows `row` and `m`
s.Cells(m, "B").Value = s.Cells(m, "B").Value s.Cells(row, "B").Value
s.Cells(m, "C").Value = s.Cells(m, "C").Value s.Cells(row, "C").Value
s.rows(row).Delete
End If 'matched a different row
Next row
End Sub
uj5u.com熱心網友回復：
使用字典稍作修改：
Sub Consolidate()
Application.ScreenUpdating = False
Dim s As Worksheet, last_row As Long
Dim row As Long, dict As Object, k As String, m As Long
Set dict = CreateObject("scripting.dictionary") 'for tracking A B vs first row occurence
Set s = Worksheets("Sheet12")
s.Activate
last_row = s.Cells(s.Rows.Count, 1).End(xlUp).row 'find the last row with data
'map all the A B combinations to the first row they occur on
For row = 3 To last_row
k = s.Cells(row, "A").Value & "~~" & s.Cells(row, "B").Value
If Not dict.exists(k) Then dict.Add k, row
Next row
For row = last_row To 3 Step -1
k = s.Cells(row, "A").Value & "~~" & s.Cells(row, "B").Value
m = dict(k) 'find first match to this row from the dictionary
If m < row Then 'earlier row?
'combine rows `row` and `m`
s.Cells(m, "C").Value = s.Cells(m, "C").Value s.Cells(row, "C").Value
s.Cells(m, "D").Value = s.Cells(m, "D").Value s.Cells(row, "D").Value
s.Rows(row).Delete
End If 'matched a different row
Next row
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/492092.html