K0st4din
Active Member
- Joined
- Feb 8, 2012
- Messages
- 488
- Office Version
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- 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
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