Sum Duplicated Cells & Then Delete

N

nwentling5

New Member
Joined
Jul 26, 2011
Messages
16
Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>

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:p></o:p>
<o:p></o:p>
Essentially, what I have is and what I need it to do is:
<o:p></o:p>
<o:p></o:p>
*After being populated by other work sheets within the workbook
Part Name: Quantity Required:<o:p></o:p>
Knob 6<o:p></o:p>
Wheel 4<o:p></o:p>
Hub 3<o:p></o:p>
Knob 5<o:p></o:p>
Wheel 3<o:p></o:p>
<o:p></o:p>
*Click Update Button
<o:p></o:p>
Part Name: Quantity Required:<o:p></o:p>
Knob 11<o:p></o:p>
Wheel 7<o:p></o:p>
Hub 3
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:preferrelative="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:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>.<o:p></o:p>
<o:p></o:p>

Thanks!<o:p></o:p>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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>
 
Upvote 0
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 ??
 
Upvote 0
I have a command button on the worksheet with the above data. And I have the above code within the command button.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Find the row when a sum on a column with certain values on rows is >= of a value
Replies
7
Views
1K
XOR LX
XOR LX
J
  • Solved
VBA to Sum and Delete Duplicate Values
2
Replies
11
Views
2K
alansidman
A
H
  • Question Question
Vba to change Excel file Properties like Title, Description, Fiscal, Period, file
Replies
0
Views
953
hemant_sonawane
H
S
  • Question Question
Correct Formulas for Mathematical Equations.
Replies
3
Views
835
siash69
S
M
  • Question Question
Counting between 2 worksheets where 1 is filtered.
Replies
0
Views
592
M4rek
M

Forum statistics

Threads
1,224,586
Messages
6,179,728
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top