VBA Delete Rows (based on multiple column criteria) <Help the new member>

K

kgaul01

New Member
Joined
Aug 26, 2013
Messages
19
Hi All, New member here, so thanks in advance for your help! I am trying to determine the best VBA code to delete duplicate rows of data; the kicker is that this is not a typical multi-column duplicate problem. So just to provide conceptual background, the architecture is set up so that a load may contain many shipments and the report output is structured in similar fashion as below. If the load does NOT have multiple shipments, then I can run VBA code to simply delete duplicate load numbers. BUT if the load DOES contain multiple shipments, then the shipment column indicates a subtotal row by using an * instead of a shipment number. So how do I delete duplicate load numbers but ensure that the code will keep the subtotal row instead of the first row in a set of duplicates?[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Load
[/TD]
[TD]Carrier
[/TD]
[TD]Shipment
[/TD]
[TD]Dollars
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]5678
[/TD]
[TD]$10
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]5679
[/TD]
[TD]$15
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]*
[/TD]
[TD]$25
[/TD]
[/TR]
[TR]
[TD]12366
[/TD]
[TD]BCD
[/TD]
[TD]9636
[/TD]
[TD]$20
[/TD]
[/TR]
[TR]
[TD]12366
[/TD]
[TD]BCD
[/TD]
[TD]9636
[/TD]
[TD]$20
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
kgaul01,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can we have two more screenshots?

One of the raw data, and, one of the results (manually formatted by you) that you are looking for.
 
Upvote 0
Hi Hiker,

Using Excel 2013, but other users may be using 2010.

This is the raw data:
[TABLE="class: cms_table_grid, align: center"]
<TBODY>[TR]
[TD]Load
[/TD]
[TD]Carrier
[/TD]
[TD]Shipment
[/TD]
[TD]Dollars
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]5678
[/TD]
[TD]$10
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]5679
[/TD]
[TD]$15
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]*
[/TD]
[TD]$25
[/TD]
[/TR]
[TR]
[TD]12366
[/TD]
[TD]BCD
[/TD]
[TD]9636
[/TD]
[TD]$20
[/TD]
[/TR]
[TR]
[TD]12366
[/TD]
[TD]BCD
[/TD]
[TD]9636
[/TD]
[TD]$20
[/TD]
[/TR]
</TBODY>[/TABLE]


This is the desired result:
[TABLE="class: cms_table_grid, align: center"]
<TBODY>[TR]
[TD]Load
[/TD]
[TD]Carrier
[/TD]
[TD]Shipment
[/TD]
[TD]Dollars
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC
[/TD]
[TD]*
[/TD]
[TD]$25
[/TD]
[/TR]
[TR]
[TD]12366
[/TD]
[TD]BCD
[/TD]
[TD]9636
[/TD]
[TD]$20
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
kgaul01,

The following is based on your current dataset. If the macro does not work on another dataset, then please supply new screenshots.

Sample raw data:


Excel 2007
ABCD
1LoadCarrierShipmentDollars
212345ABC567810
312345ABC567915
412345ABC*25
512366BCD963620
612366BCD963620
7
Sheet1


After the macro:


Excel 2007
ABCD
1LoadCarrierShipmentDollars
212345ABC*25
312366BCD963620
4
5
6
7
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub ReorgData()
' hiker95, 08/27/2013
' http://www.mrexcel.com/forum/excel-questions/722610-visual-basic-applications-delete-rows-based-multiple-column-criteria-help-new-member.html
Dim r As Long, rr As Long, lr As Long, n As Long, lrr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  If n > 1 Then
    If Cells(r + n - 1, 3) = "*" Then
      Range(Cells(r, 1), Cells(r + n - 2, 1)).ClearContents
    Else
      lrr = r + n - 1
      For rr = r To lrr
        If Cells(rr, 3) = Cells(rr + 1, 3) Then
          Cells(rr, 1).ClearContents
        End If
        rr = rr + n - 1
      Next rr
    End If
  End If
  r = r + n - 1
Next r
On Error Resume Next
Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0
Hi Hiker, this code causes Excel to crash. I have tried running it from both the personal macro workbook and the actual workbook. Here is more like what the report would look like....of course this data is just test data. I could not attach the workbook so I have pasted below:[TABLE="width: 1253"]
<tbody>[TR]
[TD] [/TD]
[TD="align: right"]08/28/2013[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Report One[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2:32 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DC[/TD]
[TD]Load[/TD]
[TD]Via[/TD]
[TD]Shipment[/TD]
[TD]Ship To[/TD]
[TD]City[/TD]
[TD]Last[/TD]
[TD]CTN[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]UNT[/TD]
[TD]DLR[/TD]
[TD]Comments[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0099999999[/TD]
[TD]MMMA[/TD]
[TD]0092222221[/TD]
[TD]MEXICO[/TD]
[TD]DF[/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]220 [/TD]
[TD="align: right"]$100.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0099999999[/TD]
[TD]MMMA[/TD]
[TD]0092222222[/TD]
[TD]MEXICO[/TD]
[TD]DF[/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]274 [/TD]
[TD="align: right"]$500.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0099999999[/TD]
[TD]MMMA[/TD]
[TD]*[/TD]
[TD]MEXICO[/TD]
[TD]DF[/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]494 [/TD]
[TD="align: right"]$600.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CN[/TD]
[TD]0088888888[/TD]
[TD]CHNA[/TD]
[TD]0082222222[/TD]
[TD]CHINA[/TD]
[TD]SHANGHAI, [/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]281 [/TD]
[TD="align: right"]$200.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CN[/TD]
[TD]0088888888[/TD]
[TD]CHNA[/TD]
[TD]0082222222[/TD]
[TD]CHINA[/TD]
[TD]SHANGHAI, [/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]281 [/TD]
[TD="align: right"]$200.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0077777777[/TD]
[TD]JPYA[/TD]
[TD]0072222222[/TD]
[TD]JAPAN[/TD]
[TD]TOKYO[/TD]
[TD="align: right"]7/22/2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2 [/TD]
[TD="align: right"]$750.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0077777777[/TD]
[TD]JPYA[/TD]
[TD]0072222222[/TD]
[TD]JAPAN[/TD]
[TD]TOKYO[/TD]
[TD="align: right"]7/22/2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2 [/TD]
[TD="align: right"]$750.00 [/TD]
[TD]WK4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0066666666[/TD]
[TD]EXFA[/TD]
[TD]0062222222[/TD]
[TD]FRANCE[/TD]
[TD]PARIS[/TD]
[TD="align: right"]8/22/2013[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]77 [/TD]
[TD="align: right"]$360.00 [/TD]
[TD]WK3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0066666666[/TD]
[TD]EXFA[/TD]
[TD]0062222222[/TD]
[TD]FRANCE[/TD]
[TD]PARIS[/TD]
[TD="align: right"]8/22/2013[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]77 [/TD]
[TD="align: right"]$360.00 [/TD]
[TD]WK3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]0055555555[/TD]
[TD]UKPA[/TD]
[TD]0052222222[/TD]
[TD]UK[/TD]
[TD]LONDON[/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD="align: right"]$7,700.00 [/TD]
[TD]WK4[/TD]
[TD="align: right"]5 [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]0055555555[/TD]
[TD]UKPA[/TD]
[TD]0052222222[/TD]
[TD]UK[/TD]
[TD]LONDON[/TD]
[TD="align: right"]8/26/2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD="align: right"]$7,700.00 [/TD]
[TD]WK4[/TD]
[TD="align: right"]5 [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1,708 [/TD]
[TD="align: right"]$19,220.00 [/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col span="3"></colgroup>[/TABLE]
 
Upvote 0
kgaul01,

Hi Hiker, this code causes Excel to crash.
Click to expand...

Of course the code (based on your first reply #1, and on reply #2) crashes, because, the macro will not run on your current dataset, reply #5.


Here is more like what the report would look like....of course this data is just test data. I could not attach the workbook
Click to expand...

In order to continue:

You can upload your workbook (with before and after worksheets) to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

If you are not able to supply a workbook with before and after worksheets, then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Last edited:
Upvote 0
Hi Hiker, sorry for the inconvenience. I have done as you instructed and uploaded a workbook with before/after sheets. Just a couple considerations that I did not think about before: 1. Both "Load" and "Shipment" columns are exported/stored as text values (I know you can set up special format types, but this is just the way the report is delivered to me). 2. The range will vary from report to report (but based on your code this looks like it has been addressed). This is the link to the workbook: https://app.box.com/s/j1iet42ms707csh7ib6h Thanks again for all your help!!
 
Upvote 0
kgaul01,

Thanks for the new workbook.

Sample raw data (columns E thru L are hidden so that the screenshot will fit correctly):


Excel 2007
ABCDMNOPQRS
108/28/20132:32 PM
2DCLoadViaShipmentUNTDLRCommentsMLPV
30099999999MMMA0092222221220$100.00WK4
40099999999MMMA0092222222274$500.00WK4
50099999999MMMA*494$600.00WK4
6CN0088888888CHNA0082222222281$200.00WK4
7CN0088888888CHNA0082222222281$200.00WK4
80077777777JPYA00722222222$750.00WK4
90077777777JPYA00722222222$750.00WK4
100066666666EXFA006222222277$360.00WK3
110066666666EXFA006222222277$360.00WK3
12UK0055555555UKPA00522222220$7,700.00WK45
13UK0055555555UKPA00522222220$7,700.00WK45
14Grand Total1,708$19,220.0010
15
Sheet1


After the new macro:


Excel 2007
ABCDMNOPQRS
108/28/20132:32 PM
2DCLoadViaShipmentUNTDLRCommentsMLPV
30099999999MMMA0092222221220$100.00WK4
40099999999MMMA0092222222274$500.00WK4
50099999999MMMA*494$600.00WK4
6CN0088888888CHNA0082222222281$200.00WK4
70077777777JPYA00722222222$750.00WK4
80066666666EXFA006222222277$360.00WK3
9UK0055555555UKPA00522222220$7,700.00WK45
10Grand Total1,348$10,210.005
11
12
13
14
15
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub ReorgDataV2()
' hiker95, 08/29/2013
' http://www.mrexcel.com/forum/excel-questions/722610-visual-basic-applications-delete-rows-based-multiple-column-criteria-help-new-member.html
Dim r As Long, rr As Long, lr As Long, n As Long, lrr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 4).End(xlUp).Row
For r = 3 To lr
  n = Application.CountIf(Columns(2), Cells(r, 2).Value)
  If n > 1 Then
    If Cells(r + n - 1, 4) = "*" Then
      'do nothing
    Else
      lrr = r + n - 1
      For rr = r To lrr
        If Cells(rr, 4) = Cells(rr + 1, 4) Then
          Cells(rr, 2).ClearContents
        End If
        rr = rr + n - 1
      Next rr
    End If
  End If
  r = r + n - 1
Next r
On Error Resume Next
Range("B3", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.
 
Last edited:
Upvote 0
Can the above macro be lil modified to compare two rows entirely and then then delete the duplicate rows

Thanks in advance
 
Upvote 0
santanuKD,

I am going to assume that your raw data structure, and, the results will be completely different than kgaul01's.


Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


In your own NEW thread include the following:

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message with a link to your NEW thread, and, I will have a look.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Locked
  • Question Question
VBA insert new rows between rows based on criteria or cell value
Replies
1
Views
498
Fluff
Fluff
M
  • Question Question
Linking between multiple sheets/workbooks
Replies
2
Views
244
MasterBash
M
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
342
Fluff
Fluff
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
843
MoonDancer
M
C
  • Solved
VBA code for copying ranges from multiple sheets to separate columns on new sheet
Replies
7
Views
244
CrishaG
C

Forum statistics

Threads
1,222,621
Messages
6,167,087
Members
452,094
Latest member
Roberto Saveru

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