Euro5
asked on
vba delete dups is not working
I am using the code
It removes some lines, but not all dups.
For instance GR 3 12x9x6 has (2) rows. Based on the code, there should only be (1) row.
It should remove anything that is the same in A, B, & F.
I am attaching data in case that helps.
Can anyone help??
sample.xlsx
Sub Delete_Dups2()
Sheets("Sheet1").Activate
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End Sub
It removes some lines, but not all dups.
For instance GR 3 12x9x6 has (2) rows. Based on the code, there should only be (1) row.
It should remove anything that is the same in A, B, & F.
I am attaching data in case that helps.
Can anyone help??
sample.xlsx
When I run it, it doesn't seem to delete any rows. Please tell me two rows that are duplicates/
please look again
there is only one row for GR 3 12x9x6
there is only one row for GR 3 12x9x6
ASKER
There are two identical rows. These are the column values
GR 3 12 9 6 12x9x6 648 166 4 9 648 4 4 Yes 0 0 -9
GR 3 12 9 6 12x9x6 648 166 4 9 648 4 4 Yes 0 0 -9
ASKER
Capricorn1 - Great point! I look at my sheet and the duplicate is all the way down on row 1648... it somehow is separated from the data by blank rows. I don't know when this is happening in my code process. But seems to be the key here!
I copied one row and insert, run the code and use the filter
the added row was deleted..
try applying filters on columns A, B, F
the added row was deleted..
try applying filters on columns A, B, F
That's why I use LastRow:
Sub Remove_Duplicates()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Sheets(1) 'Change As Needed
With Ws
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change As Needed
Else
LR = 1
End If
.Range("A1:Q" & LR).RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub
ASKER
This is what the data looks like before I run
Sub Delete_Dups2()
Sheets("Sheet1").Activate
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End Sub
sample.xlsx
Capricorn1 - Great point! I look at my sheet and the duplicate is all the way down on row 1648... it somehow is separated from the data by blank rows. I don't know when this is happening in my code process. But seems to be the key here!And if you run your existing code it does delete that row.
Try runny my code and see
ASKER
Shums,
Trying that code also
Trying that code also
What point I am missing here?
No matter if there are blank rows in the data set, the code will delete the duplicate row by all means.
I tested the code in both the attached workbooks and the specified row got deleted.
No matter if there are blank rows in the data set, the code will delete the duplicate row by all means.
I tested the code in both the attached workbooks and the specified row got deleted.
Also the code is implemented on whole columns so figuring out the last row doesn't make any sense to me.
your code is working, only 750 rows remain after running the codes
What point I am missing here?You're not. When I first said that nothing was deleted I didn't realize there were a bunch of blank rows followed by the duplicate row.
ASKER
I can't figure it out. Something is wrong and I have to step through some code.
Did you try the same code in the workbook you uploaded here? And didn't it work as expected?
If so, just save and close your file, reboot the system and run the code again and see if this works then. :)
If so, just save and close your file, reboot the system and run the code again and see if this works then. :)
apply filter to your workbook see the dropdown box on row1
zsample.xlsx
zsample.xlsx
ASKER
Capricorn1 - your code is working, only 750 rows remain after running the codes OK, now I am trying
Not when I run it. When I run it, it gives me 750 rows AND ONE ROW OF DATA IN ROW 1647.
Originally the duplicate data was in rows 593, 1511, and 1647.
What is happening is that it give me a lot of empty rows then a row of data in 1647.
I tried the new code below and got same result.
Not when I run it. When I run it, it gives me 750 rows AND ONE ROW OF DATA IN ROW 1647.
Originally the duplicate data was in rows 593, 1511, and 1647.
What is happening is that it give me a lot of empty rows then a row of data in 1647.
I tried the new code below and got same result.
Sub Delete_Dups2()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Sheets("Change box size") 'Change As Needed
With Ws
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change As Needed
Else
LR = 1
End If
.Range("A1:Q" & LR).RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub
<What is happening is that it give me a lot of empty rows then a row of data in 1647.>
something wrong with your excel app
something wrong with your excel app
Euro your workbook as posted has 750 rows plus one row in 1647. If you run your code as shown here. row 1647 (the duplicate) is removed.
Sub Delete_Dups2()
Sheets("Sheet1").Activate
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End Sub
ASKER
Capricorn1 - Ok, thanks. I'm really stumped.
I'm really stumped.About what? Duplicates? If so there's no problem. Blank Rows? Then post a workbook before the blank rows are added and include the code that produces them.
could it be your monitor is not responding quickly, just a guess...
ASKER
Martin - If I run the code on the sample I confirm that it works.
If I run it in my project...nothing. I have been stuck on this for hours.
I will take your suggestion...
If I run it in my project...nothing. I have been stuck on this for hours.
I will take your suggestion...
Can you post tour actual workbook?
Is the sheet with the dupes in that workbook called "Sheet1"?
Is the sheet with the dupes in that workbook called "Sheet1"?
@Euro
As I said, save and close your file, reboot your system and try to run the code again on your actual workbook and see if you have luck this time.
Also when some data seem to look like duplicate in reality it may not be duplicated. So compare those rows with the simple formulas in one blank row like below...
In a blank cell in column A
=A593=A1647
In a blank cell in column B
=B593=B1647
In a blank cell in column F
=F593=F1647
If you get True for all the three formulas that means the row is duplicated based on column A, B and F. And if you get False in any of the formula cells, that simply means one of the cells has some extra character in it like space or a new line character etc.
Replace the 593 and 1647 in the above formulas with the row numbers of the data which you think is duplicated and not being deleted by the code.
As I said, save and close your file, reboot your system and try to run the code again on your actual workbook and see if you have luck this time.
Also when some data seem to look like duplicate in reality it may not be duplicated. So compare those rows with the simple formulas in one blank row like below...
In a blank cell in column A
=A593=A1647
In a blank cell in column B
=B593=B1647
In a blank cell in column F
=F593=F1647
If you get True for all the three formulas that means the row is duplicated based on column A, B and F. And if you get False in any of the formula cells, that simply means one of the cells has some extra character in it like space or a new line character etc.
Replace the 593 and 1647 in the above formulas with the row numbers of the data which you think is duplicated and not being deleted by the code.
ASKER
Subodh Tiwari (Neeraj) I ran your test and got TRUE for all three, so I do have the duplicates.
I am attaching the project, the sheet in question is Change in box size.
This is the state JUST BEFORE I run the DeleteDups2
Can anyone tell me if they see what I do after the code is run?
I am attaching the project, the sheet in question is Change in box size.
This is the state JUST BEFORE I run the DeleteDups2
Can anyone tell me if they see what I do after the code is run?
Sub Delete_Dups2()
Sheets("Change box size").Activate
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End Sub
Sample.xlsm
ASKER
This worked
Sub DeleteRows()
With ActiveSheet
Set rng = Range("A1", Range("Q1").End(xlDown))
rng.RemoveDuplicates Columns:=Array(1, 2, 6), Header:=xlYes
End With
End Sub
ASKER
No, wrong about that. same thing is happening.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
You're Welcome Euro! Eventually it worked for you :)
You may also try the below code....
Sub DeleteDuplicateRows()
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("R2:R" & lr).Formula = "=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,F$2:F2,F2)>1,NA(),"""")"
Range("R2:R" & lr).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Columns("R").Delete
Application.ScreenUpdating = True
End Sub
Neeraj,
I already tried this way, but LR is not reading correctly in his worksheet, so I extended the range from 9999 to 1 step -1 and it worked eventually.
I already tried this way, but LR is not reading correctly in his worksheet, so I extended the range from 9999 to 1 step -1 and it worked eventually.
But I tried it on the latest sample workbook and it worked just fine. :)