Mixing
Remove Row(s) Based on Duplicate Values When Comparing 2 Row Cells To Below Row Cells in Same Column in...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
2
I am attempting to remove (mostly) duplicate rows from a very large spreadsheet.
I can tell that the row is duplicate if the values in two cells per row are the same.
Here is an example:
1 a ewq
1 e weq
1 h ewq
2 b ddsfa
2 b as
2 i d
3 c fdsa
3 f ads
4 d fd
4 g as
In this example, the fourth and fifth rows would be duplicate because the values in column "A" and column "B" are the same. The deciding values will always be found in the same columns.
I would like to get rid of either the fourth or fifth row based on the duplicate status and shift the rows up.
I'm not even sure if this is close, but this is what I have so far (I'm getting a mismatch error):
Sub MasterRemoveDuplicates()
Dim Master As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Master = Workbooks("Master.csv").Worksheets("Master")
Last = 1
For i = 1 To 18211
If Range("A" & i) And Range("B" & i) <> Range("A" & (i + 1)) And Range("B" & (i + 1)) Then
Worksheets("Master").Rows(Last).Delete Shift:=xlShiftUp
Last = i + 1
Master.Activate
End If
Next i
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Any thoughts on how I could achieve this would be greatly appreciated!
P.S. Everything is in the same worksheet.
excel vba excel-vba
asked Jan 3 at 9:32
John SmithJohn Smith
246
add a comment |
2
I am attempting to remove (mostly) duplicate rows from a very large spreadsheet.
I can tell that the row is duplicate if the values in two cells per row are the same.
Here is an example:
1 a ewq
1 e weq
1 h ewq
2 b ddsfa
2 b as
2 i d
3 c fdsa
3 f ads
4 d fd
4 g as
In this example, the fourth and fifth rows would be duplicate because the values in column "A" and column "B" are the same. The deciding values will always be found in the same columns.
I would like to get rid of either the fourth or fifth row based on the duplicate status and shift the rows up.
I'm not even sure if this is close, but this is what I have so far (I'm getting a mismatch error):
Sub MasterRemoveDuplicates()
Dim Master As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Master = Workbooks("Master.csv").Worksheets("Master")
Last = 1
For i = 1 To 18211
If Range("A" & i) And Range("B" & i) <> Range("A" & (i + 1)) And Range("B" & (i + 1)) Then
Worksheets("Master").Rows(Last).Delete Shift:=xlShiftUp
Last = i + 1
Master.Activate
End If
Next i
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Any thoughts on how I could achieve this would be greatly appreciated!
P.S. Everything is in the same worksheet.
excel vba excel-vba
asked Jan 3 at 9:32
John SmithJohn Smith
246
add a comment |
2
2
2
2
I am attempting to remove (mostly) duplicate rows from a very large spreadsheet.
I can tell that the row is duplicate if the values in two cells per row are the same.
Here is an example:
1 a ewq
1 e weq
1 h ewq
2 b ddsfa
2 b as
2 i d
3 c fdsa
3 f ads
4 d fd
4 g as
In this example, the fourth and fifth rows would be duplicate because the values in column "A" and column "B" are the same. The deciding values will always be found in the same columns.
I would like to get rid of either the fourth or fifth row based on the duplicate status and shift the rows up.
I'm not even sure if this is close, but this is what I have so far (I'm getting a mismatch error):
Sub MasterRemoveDuplicates()
Dim Master As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Master = Workbooks("Master.csv").Worksheets("Master")
Last = 1
For i = 1 To 18211
If Range("A" & i) And Range("B" & i) <> Range("A" & (i + 1)) And Range("B" & (i + 1)) Then
Worksheets("Master").Rows(Last).Delete Shift:=xlShiftUp
Last = i + 1
Master.Activate
End If
Next i
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Any thoughts on how I could achieve this would be greatly appreciated!
P.S. Everything is in the same worksheet.
excel vba excel-vba
asked Jan 3 at 9:32
John SmithJohn Smith
246
I am attempting to remove (mostly) duplicate rows from a very large spreadsheet.
I can tell that the row is duplicate if the values in two cells per row are the same.
Here is an example:
1 a ewq
1 e weq
1 h ewq
2 b ddsfa
2 b as
2 i d
3 c fdsa
3 f ads
4 d fd
4 g as
In this example, the fourth and fifth rows would be duplicate because the values in column "A" and column "B" are the same. The deciding values will always be found in the same columns.
I would like to get rid of either the fourth or fifth row based on the duplicate status and shift the rows up.
I'm not even sure if this is close, but this is what I have so far (I'm getting a mismatch error):
Sub MasterRemoveDuplicates()
Dim Master As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Master = Workbooks("Master.csv").Worksheets("Master")
Last = 1
For i = 1 To 18211
If Range("A" & i) And Range("B" & i) <> Range("A" & (i + 1)) And Range("B" & (i + 1)) Then
Worksheets("Master").Rows(Last).Delete Shift:=xlShiftUp
Last = i + 1
Master.Activate
End If
Next i
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Any thoughts on how I could achieve this would be greatly appreciated!
P.S. Everything is in the same worksheet.
excel vba excel-vba
excel vba excel-vba
asked Jan 3 at 9:32
John SmithJohn Smith
246
asked Jan 3 at 9:32
John SmithJohn Smith
246
asked Jan 3 at 9:32
John SmithJohn Smith
246
asked Jan 3 at 9:32
John SmithJohn Smith
246
asked Jan 3 at 9:32
John SmithJohn Smith
246
246
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
3
You're trying to reinvent the wheel. There is a ready-made RemoveDuplicates command.
Sub MasterRemoveDuplicates()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks("Master.csv").Worksheets("Master")
With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 2))
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End With
End With
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
answered Jan 3 at 11:37
user10862412user10862412
4023
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
add a comment |
1
You have a mistake in if statement
try this:
If Range("A" & i) <> Range("A" & (i + 1)) And Range("B" & i) <> Range("B" & (i + 1)) Then
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019526%2fremove-rows-based-on-duplicate-values-when-comparing-2-row-cells-to-below-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
You're trying to reinvent the wheel. There is a ready-made RemoveDuplicates command.
Sub MasterRemoveDuplicates()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks("Master.csv").Worksheets("Master")
With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 2))
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End With
End With
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
answered Jan 3 at 11:37
user10862412user10862412
4023
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
add a comment |
3
You're trying to reinvent the wheel. There is a ready-made RemoveDuplicates command.
Sub MasterRemoveDuplicates()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks("Master.csv").Worksheets("Master")
With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 2))
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End With
End With
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
answered Jan 3 at 11:37
user10862412user10862412
4023
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
add a comment |
3
3
3
You're trying to reinvent the wheel. There is a ready-made RemoveDuplicates command.
Sub MasterRemoveDuplicates()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks("Master.csv").Worksheets("Master")
With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 2))
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End With
End With
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
answered Jan 3 at 11:37
user10862412user10862412
4023
You're trying to reinvent the wheel. There is a ready-made RemoveDuplicates command.
Sub MasterRemoveDuplicates()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks("Master.csv").Worksheets("Master")
With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 2))
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End With
End With
MsgBox "Completed!", vbInformation, ""
reset_settings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
answered Jan 3 at 11:37
user10862412user10862412
4023
edited Jan 3 at 14:13
answered Jan 3 at 11:37
user10862412user10862412
4023
answered Jan 3 at 11:37
user10862412user10862412
4023
answered Jan 3 at 11:37
user10862412user10862412
4023
4023
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
add a comment |
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
Wow. Thank you for this! I knew I was overthinking it. The only correction I had to make was to change '.Offset(0,1)' to '.Offset(0,2)' so the entire used part of the row (3 columns have info in the example above) would be deleted rather than only the first two. For anyone who reads this, the last value in 'Offset' will be the upper limit of your used cells in the row to be purged. Thanks again!
– John Smith
Jan 3 at 13:46
add a comment |
1
You have a mistake in if statement
try this:
If Range("A" & i) <> Range("A" & (i + 1)) And Range("B" & i) <> Range("B" & (i + 1)) Then
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
add a comment |
1
You have a mistake in if statement
try this:
If Range("A" & i) <> Range("A" & (i + 1)) And Range("B" & i) <> Range("B" & (i + 1)) Then
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
add a comment |
1
1
1
You have a mistake in if statement
try this:
If Range("A" & i) <> Range("A" & (i + 1)) And Range("B" & i) <> Range("B" & (i + 1)) Then
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
You have a mistake in if statement
try this:
If Range("A" & i) <> Range("A" & (i + 1)) And Range("B" & i) <> Range("B" & (i + 1)) Then
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
answered Jan 3 at 11:17
Jimmy M.Jimmy M.
763
763
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
add a comment |
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
Thanks @Jimmy M. ! Dumb mistake on my part...I tried running it with the fix and don't get an error anymore. Unfortunately, my shoddy code deleted more than it was supposed to. Since user10862412's code worked, I'm just going to abandon mine and go with his. Thanks again for your help!
– John Smith
Jan 3 at 13:56
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019526%2fremove-rows-based-on-duplicate-values-when-comparing-2-row-cells-to-below-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
