Sum Duplicated Cells & Then Delete

[nwentling5]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have read over numerous threads on how to do this, but I am still not able to piece together the correct code to make it work.<o></o>

What I am trying to do is write some VBA code to run through a master part list, add up the duplicated part's quantity values and then delete the duplicated rows that housed the duplicated cells. <o></o>
<o></o>
Essentially, what I have is and what I need it to do is:
<o></o>
<o></o>
*After being populated by other work sheets within the workbook
Part Name: Quantity Required:<o></o>
Knob 6<o></o>
Wheel 4<o></o>
Hub 3<o></o>
Knob 5<o></o>
Wheel 3<o></o>
<o></o>
*Click Update Button
<o></o>
Part Name: Quantity Required:<o></o>
Knob 11<o></o>
Wheel 7<o></o>
Hub 3
<o></o>
<o></o>
I must note that I am using 2007 Excel and the loops need to be able to work dynamically since the master list will be updated regularly from other worksheets.
<o></o>
<o></o>
Also I have looked at pivot tables and I have messed around with them, but I wasn't able to create exactly what I was looking for.
<o></o>
<o></o>
If anyone can provide me a direction or some basic code on achieving this it would be greatly appreciated! I feel like it is probably something easy to code in VBA, but I have worked myself into a confused & lost state<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>.<o></o>
<o></o>

Thanks!<o></o>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG27Jul54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, Array(Dn, Dn.Offset(, 1))
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
            Q(1) = Q(1) + Dn.Offset(, 1)
            Q(0).Offset(, 1) = Q(1)
            .Item(Dn.Value) = Q
                [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                     [COLOR="Navy"]Set[/COLOR] nRng = Dn
                [COLOR="Navy"]Else[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nwentling5]

Hey MickG thanks for the reply!

I tried throwing it into a test excel sheet with the first column (A) being the name of the part and the second column (B) being the quantity, but after I tried running it I got the following error: "Run-time error '91': Object variable or With block variable not set.

It also highlighted this line: nRng.EntireRow.Delete

I will try to mess around with it and figure it out, but I am not fully sure on how to correct this error. I'll try googling around though.

Thanks again for the reply.

 

[MickG]

That last line should actually be as below, because once you've run the code there are no duplicates therefore nRng is "Nothing"

If Not nRng Is Nothing Then
nRng.EntireRow.Delete
End If

 

[nwentling5]

I changed it to what you said it needs to be and when I click update it runs, but nothing changes on the spread sheet. When I debug it it seems to step through fine by it never jumps into the "Else" or adds up anything.
I would attached the test workbook, but my works network restricts the html maker site.

However the current code is:

Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim nRng As Range
Dim Q
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
    For Each Dn In Rng
        If Not .Exists(Dn.Value) Then
            .Add Dn.Value, Array(Dn, Dn.Offset(, 1))
        Else
            Q = .Item(Dn.Value)
            Q(1) = Q(1) + Dn.Offset(, 1)
            Q(0).Offset(, 1) = Q(1)
            .Item(Dn.Value) = Q
                If nRng Is Nothing Then
                     Set nRng = Dn
                Else
                    Set nRng = Union(nRng, Dn)
                End If
        End If
Next
End With
If Not nRng Is Nothing Then
nRng.EntireRow.Delete
End If
End Sub

 

[MickG]

If you place a border around each cell in the example range , so it forms a grid you should be able to copy and paste directly into the thread.
If that fails jost post a couple nof lines where the data is different, I'll make the rest up.
Nb:- Your duplicate data should be in column "A" and the count for each in column "B".

 

[nwentling5]

My Ranges are:
A B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Knob</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Wheel </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Hub</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Knob</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD></TR></TBODY></TABLE>

 

[MickG]

This is the result:-
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl29 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=128 colSpan=2 height=17>A B

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Knob</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>8</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18 x:str="Wheel ">Wheel </TD><TD class=xl28 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17>Hub</TD><TD class=xl28 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3</TD></TR></TBODY></TABLE>

How are you running the code ??
CommandButton ??
Module ?? . If Basic Module, is the data sheet the active sheet
Other ??

 

[nwentling5]

I have a command button on the worksheet with the above data. And I have the above code within the command button.

 

[MickG]

Did you get a results.
Are you running excel for Windows or Mac.