Macro to Remove duplicates on three criteria

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
488
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello everyone,
I turn to you asking for help, about a week ago I made an inquiry on this site for a slight change in a macro that I work long and I am very pleased with it.
Colleague who did this macro wrote me to keep the topic on the top, but I was embarrassed to go up artificially above and above, etc., but then no one answers me that can help me with the macro.
With the macro, which will get (and you can see it in the other site) remove duplicates on three criteria, but only in one month (and works incredibly well - has not given me an error).
I need and I do not know how to do it to me shows a (Application.InputBox (Prompt: =) - to me and asked to delete again three criteria, but desirable than a month back months.
Ie if hypothetically assume that I have the 12th (December) month (no day) and Application.InputBox (Prompt: = "How many months back you want to delete the information, excluding the last, ie the 12th?") - And if I write six months back, ie (6) should get the result that I have done in sheet2 (red).
But this red sheet is only to see the result, but actually have to come after the same deletion in Sheet1. Same if I write 7 months back, or 4 months , or 15 months, ect....
Upload examples and my macro hoping that might help me.
Also ask you if there is anything you do not understand, I will try to explain.
Thank you in advance.
Link to file -
Post a link to the file, because here I have no option to attach to the site itself.Please for your assistance in finding a solution, because every month I 'expire eyes "to manually filter information on three criteria and to delete a thousand lines.
I hope it is possible to remake this macro
Code:
Sub Dublikati_psihiatri_J()
'Comment marks Description:
     '' (2 marks.) Instructions to K0st4din. Delete on satisfaction.
     '''' (4 marks.) Section Heading or major code segment.
     ' (1 mark.) Programmer reason for choosing to code this way.
     
     '' In VBA Editor, move Section Headings to far left, (Delete leading spaces.)
     
     
    Const DateCol As Long = 4 ''If sheet layout changes, only change these Constants
    Const NumCol As Long = 7 ''to maintain code.
    Const CodeCol As Long = 10
    Const ShtName As String = "my name sheet"
     
     
    Dim sh As Worksheet
    Dim lastrow As Long
    Dim rw As Long 'Row Index
     
     
    Set sh = Sheets(ShtName)
    Application.ScreenUpdating = False
     
     ''''Sort for Processing
    With sh
        lastrow = .Cells(Rows.Count, "J").End(xlUp).Row
         
         
         'Changed Sort Order1 to Descending to preserve "no delete" rows
        Range("A1").CurrentRegion.Sort Key1:=Columns(NumCol), Order1:=xlDescending, _
        Key2:=Columns(CodeCol), Order2:=xlAscending, _
        Key3:=Columns(DateCol), Order3:=xlAscending, _
        Header:=xlYes
         
         
         '''''Processing. Delete Rows per Criteria.
        For rw = lastrow To 2 Step -1
             'Fixed Cell assignments. Column J is not Columns(11)
            If .Cells(rw, CodeCol).Value = .Cells(rw - 1, CodeCol).Value _
            And .Cells(rw, NumCol).Value = .Cells(rw - 1, NumCol).Value Then
                 'Next line Raises "Type Mismatch" Error when Anded with above?!?!
                If Month(.Cells(rw, DateCol).Value) = Month(.Cells(rw - 1, DateCol).Value) Then
                     'Edit Resize as needed
                    .Cells(rw, 1).Resize(1, CodeCol + 19).Delete Shift:=xlUp
                End If
            End If
        Next rw
         
         
         ''''Restore sort for viewing.
         '' Adjust sort as desired
        Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, _
        Key2:=Columns(DateCol), Order2:=xlAscending, _
        Key3:=Columns(NumCol), Order3:=xlAscending, _
        Header:=xlYes
         
    End With
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hello guys
I beg of you at least someone to share with me whether it is possible to obtain such a macro. Of course, it may be another macro, but does the same job.
Thank you in advance
 
Upvote 0
Hello everyone,
really ask for some assistance from you, I can not do it alone this macro.
Is it possible to do something about it?
 
Upvote 0
Hello,
I would like to ask you, is there anything that is not clear in my request. Pray for an answer from you. Thank you in advance
 
Upvote 0
Hello again
apparently there is no way to make a macro in the way that I look.
And that can be done only to delete six months back again the same three criteria?
Thank you warmly.
 
Upvote 0

Forum statistics

Threads
1,214,584
Messages
6,120,384
Members
448,956
Latest member
JPav

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