VBA: looping around VLOOKUP to delete row on success
I am trying loop back over data in a worksheet in Excel 365 and when certain conditions are met, delete the row the condition applies to. All is well until I use the workbook VLookUp function as a part of the final condition - I need to delete the row if any of the conditions are true or if the VLookup is successful (an error in the VLookUp would mean that the row should be kept). What I currently have is:
On Error Resume Next
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or _
Condition2 Or _
WorksheetFunction.VLookup(Cells(i, lcCol).Value, divRange, 1, False) Then
If Err.Number = 0 Then
Rows(i).Delete
End If
End If
Next i
The above flips to the other workbook (divWorkbook) and does not do what is intended. How would I keep focus on the current workbook and sheet and apply the delete when the vlookup is successful but not when it isn't?
Thanks,
Ben
excel vba excel-vba vlookup
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:04
BenBen
196
add a comment |
I am trying loop back over data in a worksheet in Excel 365 and when certain conditions are met, delete the row the condition applies to. All is well until I use the workbook VLookUp function as a part of the final condition - I need to delete the row if any of the conditions are true or if the VLookup is successful (an error in the VLookUp would mean that the row should be kept). What I currently have is:
On Error Resume Next
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or _
Condition2 Or _
WorksheetFunction.VLookup(Cells(i, lcCol).Value, divRange, 1, False) Then
If Err.Number = 0 Then
Rows(i).Delete
End If
End If
Next i
The above flips to the other workbook (divWorkbook) and does not do what is intended. How would I keep focus on the current workbook and sheet and apply the delete when the vlookup is successful but not when it isn't?
Thanks,
Ben
excel vba excel-vba vlookup
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:04
BenBen
196
Try specifying the worksheet you want to delete the rows from: ex:Worksheet("Sheet1").Rows(i).Delete
– cybernetic.nomad
Nov 26 '18 at 20:09
Thanks. I did make it more specific.
– Ben
Dec 1 '18 at 21:43
add a comment |
I am trying loop back over data in a worksheet in Excel 365 and when certain conditions are met, delete the row the condition applies to. All is well until I use the workbook VLookUp function as a part of the final condition - I need to delete the row if any of the conditions are true or if the VLookup is successful (an error in the VLookUp would mean that the row should be kept). What I currently have is:
On Error Resume Next
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or _
Condition2 Or _
WorksheetFunction.VLookup(Cells(i, lcCol).Value, divRange, 1, False) Then
If Err.Number = 0 Then
Rows(i).Delete
End If
End If
Next i
The above flips to the other workbook (divWorkbook) and does not do what is intended. How would I keep focus on the current workbook and sheet and apply the delete when the vlookup is successful but not when it isn't?
Thanks,
Ben
excel vba excel-vba vlookup
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:04
BenBen
196
I am trying loop back over data in a worksheet in Excel 365 and when certain conditions are met, delete the row the condition applies to. All is well until I use the workbook VLookUp function as a part of the final condition - I need to delete the row if any of the conditions are true or if the VLookup is successful (an error in the VLookUp would mean that the row should be kept). What I currently have is:
On Error Resume Next
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or _
Condition2 Or _
WorksheetFunction.VLookup(Cells(i, lcCol).Value, divRange, 1, False) Then
If Err.Number = 0 Then
Rows(i).Delete
End If
End If
Next i
The above flips to the other workbook (divWorkbook) and does not do what is intended. How would I keep focus on the current workbook and sheet and apply the delete when the vlookup is successful but not when it isn't?
Thanks,
Ben
excel vba excel-vba vlookup
excel vba excel-vba vlookup
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:04
BenBen
196
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:04
BenBen
196
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:15
Pᴇʜ
22.8k62850
22.8k62850
asked Nov 26 '18 at 20:04
BenBen
196
asked Nov 26 '18 at 20:04
BenBen
196
asked Nov 26 '18 at 20:04
BenBen
196
196
Try specifying the worksheet you want to delete the rows from: ex:Worksheet("Sheet1").Rows(i).Delete
– cybernetic.nomad
Nov 26 '18 at 20:09
Thanks. I did make it more specific.
– Ben
Dec 1 '18 at 21:43
add a comment |
Try specifying the worksheet you want to delete the rows from: ex:Worksheet("Sheet1").Rows(i).Delete
– cybernetic.nomad
Nov 26 '18 at 20:09
Thanks. I did make it more specific.
– Ben
Dec 1 '18 at 21:43
Try specifying the worksheet you want to delete the rows from: ex:
Worksheet("Sheet1").Rows(i).Delete– cybernetic.nomad
Nov 26 '18 at 20:09
Try specifying the worksheet you want to delete the rows from: ex:
Worksheet("Sheet1").Rows(i).Delete– cybernetic.nomad
Nov 26 '18 at 20:09
Thanks. I did make it more specific.
– Ben
Dec 1 '18 at 21:43
Thanks. I did make it more specific.
– Ben
Dec 1 '18 at 21:43
add a comment |
1 Answer
1
active
oldest
votes
I'd drop the On Error Resume Next and instead use Application.Match, testing the return value for an error.
Also, as noted by @cybernetic.nomad, be more explicit about what sheet you're working on.
Dim shtData As Worksheet
Set shtData = ActiveSheet ' or whatever
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or Condition2 Or _
Not IsError(Application.Match(shtData.Cells(i, lcCol).Value, divRange, 0)) Then
shtData.Rows(i).Delete
End If
Next i
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one lineshtData.Activatein order to keep or at least return the focus to the current sheet.
– Ben
Dec 1 '18 at 21:47
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
});
}
});
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%2f53488220%2fvba-looping-around-vlookup-to-delete-row-on-success%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'd drop the On Error Resume Next and instead use Application.Match, testing the return value for an error.
Also, as noted by @cybernetic.nomad, be more explicit about what sheet you're working on.
Dim shtData As Worksheet
Set shtData = ActiveSheet ' or whatever
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or Condition2 Or _
Not IsError(Application.Match(shtData.Cells(i, lcCol).Value, divRange, 0)) Then
shtData.Rows(i).Delete
End If
Next i
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one lineshtData.Activatein order to keep or at least return the focus to the current sheet.
– Ben
Dec 1 '18 at 21:47
add a comment |
I'd drop the On Error Resume Next and instead use Application.Match, testing the return value for an error.
Also, as noted by @cybernetic.nomad, be more explicit about what sheet you're working on.
Dim shtData As Worksheet
Set shtData = ActiveSheet ' or whatever
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or Condition2 Or _
Not IsError(Application.Match(shtData.Cells(i, lcCol).Value, divRange, 0)) Then
shtData.Rows(i).Delete
End If
Next i
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one lineshtData.Activatein order to keep or at least return the focus to the current sheet.
– Ben
Dec 1 '18 at 21:47
add a comment |
I'd drop the On Error Resume Next and instead use Application.Match, testing the return value for an error.
Also, as noted by @cybernetic.nomad, be more explicit about what sheet you're working on.
Dim shtData As Worksheet
Set shtData = ActiveSheet ' or whatever
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or Condition2 Or _
Not IsError(Application.Match(shtData.Cells(i, lcCol).Value, divRange, 0)) Then
shtData.Rows(i).Delete
End If
Next i
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
I'd drop the On Error Resume Next and instead use Application.Match, testing the return value for an error.
Also, as noted by @cybernetic.nomad, be more explicit about what sheet you're working on.
Dim shtData As Worksheet
Set shtData = ActiveSheet ' or whatever
divFile = "C:....workbook.xlsx"
Set divWorkbook = Workbooks.Open(divFile)
Set divRange = divWorkbook.Worksheets("Sheet3").Range("B2:B274")
For i = lastRow To firstDataRow Step -1
If Condition1 Or Condition2 Or _
Not IsError(Application.Match(shtData.Cells(i, lcCol).Value, divRange, 0)) Then
shtData.Rows(i).Delete
End If
Next i
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
answered Nov 26 '18 at 20:47
Tim WilliamsTim Williams
87k97086
87k97086
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one lineshtData.Activatein order to keep or at least return the focus to the current sheet.
– Ben
Dec 1 '18 at 21:47
add a comment |
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one lineshtData.Activatein order to keep or at least return the focus to the current sheet.
– Ben
Dec 1 '18 at 21:47
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one line
shtData.Activate in order to keep or at least return the focus to the current sheet.– Ben
Dec 1 '18 at 21:47
Thanks for your help. I fashioned the solution based on your response and it worked. I did add one line
shtData.Activate in order to keep or at least return the focus to the current sheet.– Ben
Dec 1 '18 at 21:47
add a comment |
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.
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%2f53488220%2fvba-looping-around-vlookup-to-delete-row-on-success%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
Try specifying the worksheet you want to delete the rows from: ex:
Worksheet("Sheet1").Rows(i).Delete– cybernetic.nomad
Nov 26 '18 at 20:09
Thanks. I did make it more specific.
– Ben
Dec 1 '18 at 21:43