Sum Duplicated Cells & Then Delete

nwentling5 · Jul 26, 2011

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><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" o

referrelative="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><v

ath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v

ath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>.<o

></o

>
<o

></o

>

Thanks!<o

></o

>

MickG · Jul 27, 2011

Try this:-

Code:

[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 · Jul 27, 2011

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 · Jul 27, 2011

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

Code:

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

nwentling5 · Jul 27, 2011

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:

Code:

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 · Jul 27, 2011

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 · Jul 27, 2011

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 · Jul 27, 2011

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 · Jul 27, 2011

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

MickG · Jul 27, 2011

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

Sum Duplicated Cells & Then Delete

nwentling5

New Member

MickG

MrExcel MVP

nwentling5

New Member

MickG

MrExcel MVP

nwentling5

New Member

MickG

MrExcel MVP

nwentling5

New Member

MickG

MrExcel MVP

nwentling5

New Member

MickG

MrExcel MVP

Similar threads

Share this page

Sum Duplicated Cells & Then Delete

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock