rahul_shokeen
New Member
- Joined
- Jun 13, 2022
- Messages
- 3
- Office Version
- 365
- 2021
- Platform
- Windows
I want to find duplicates in Column A & Column K and combine rows from D to J. Currently, I am using Kutools to combine rows. I want to use VBA to summarize the data.
Input
Desired Output
The code is working perfectly for deleting duplicates in columns A & K. Also, It combines column F. But I don't know how to add other columns D, E, G, H, I, and J in the code to sum them. Please help.
Excel Link:
Input
Desired Output
VBA Code:
Sub HSNTotal()
Dim Cl As Range, Rng As Range
Dim Txt As String
With CreateObject("scripting.dictionary")
For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
Txt = Cl.Value & Cl.Offset(, 10).Value
If Not .Exists(Txt) Then
.Add Txt, Cl.Offset(, 5)
Else
.Item(Txt).Value = .Item(Txt).Value + Cl.Offset(, 5).Value
.Item(Txt).Value = Round(.Item(Txt).Value, 2)
If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
End If
Next Cl
End With
If Not Rng Is Nothing Then Rng.EntireRow.Delete
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "F") = 0 Then
Rows(r).Delete
End If
Next r
End Sub
The code is working perfectly for deleting duplicates in columns A & K. Also, It combines column F. But I don't know how to add other columns D, E, G, H, I, and J in the code to sum them. Please help.
Excel Link:
HSN Merge.xlsm
drive.google.com