Deleting Multiple Rows based on a Set Criteria
0
Sub Macro()
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(i, 14).Value2 = "APPLE" Then
Rows(i).Delete
End If
Next i
Dim f As Long
For f = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(f, 14).Value2 = "NAME" Then
Rows(f).Delete
End If
Next f
End Sub
I have the above mentioned code to delete all the rows that have apple and name on them, If possible I would like excel to execute the code in one or two lines. Your help would be greatly appreciated!
excel vba
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
asked Dec 27 '18 at 20:14
Hayk
272
add a comment |
0
Sub Macro()
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(i, 14).Value2 = "APPLE" Then
Rows(i).Delete
End If
Next i
Dim f As Long
For f = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(f, 14).Value2 = "NAME" Then
Rows(f).Delete
End If
Next f
End Sub
I have the above mentioned code to delete all the rows that have apple and name on them, If possible I would like excel to execute the code in one or two lines. Your help would be greatly appreciated!
excel vba
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
asked Dec 27 '18 at 20:14
Hayk
272
Fastest method for deleting rows would be to use AutoFilter
– JohnyL
Dec 27 '18 at 20:29
add a comment |
0
0
0
Sub Macro()
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(i, 14).Value2 = "APPLE" Then
Rows(i).Delete
End If
Next i
Dim f As Long
For f = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(f, 14).Value2 = "NAME" Then
Rows(f).Delete
End If
Next f
End Sub
I have the above mentioned code to delete all the rows that have apple and name on them, If possible I would like excel to execute the code in one or two lines. Your help would be greatly appreciated!
excel vba
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
asked Dec 27 '18 at 20:14
Hayk
272
Sub Macro()
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(i, 14).Value2 = "APPLE" Then
Rows(i).Delete
End If
Next i
Dim f As Long
For f = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
If Cells(f, 14).Value2 = "NAME" Then
Rows(f).Delete
End If
Next f
End Sub
I have the above mentioned code to delete all the rows that have apple and name on them, If possible I would like excel to execute the code in one or two lines. Your help would be greatly appreciated!
excel vba
excel vba
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
asked Dec 27 '18 at 20:14
Hayk
272
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
asked Dec 27 '18 at 20:14
Hayk
272
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
edited Dec 27 '18 at 20:18
cybernetic.nomad
2,2051820
2,2051820
asked Dec 27 '18 at 20:14
Hayk
272
asked Dec 27 '18 at 20:14
Hayk
272
asked Dec 27 '18 at 20:14
Hayk
272
272
Fastest method for deleting rows would be to use AutoFilter
– JohnyL
Dec 27 '18 at 20:29
add a comment |
Fastest method for deleting rows would be to use AutoFilter
– JohnyL
Dec 27 '18 at 20:29
Fastest method for deleting rows would be to use AutoFilter
– JohnyL
Dec 27 '18 at 20:29
Fastest method for deleting rows would be to use AutoFilter
– JohnyL
Dec 27 '18 at 20:29
add a comment |
2 Answers
2
active
oldest
votes
1
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
IF Cells(i, 14).Value2 = "APPLE" OR Cells(i, 14).Value2 = "NAME" THEN Rows(i).Delete
Next i
answered Dec 27 '18 at 20:17
Cyril
2,4811822
add a comment |
1
The fast way to delete rows is using AutoFilter:
Sub FastDelete()
Dim rng As Range, rngVisible As Range
'//Remove filter if any
ActiveSheet.AutoFilterMode = False
'// Get range of only one column (N)
Set rng = Range(Cells(1, 14), Cells(Rows.Count, 14).End(xlUp))
'// Field:=1 because filter has only one field
rng.AutoFilter Field:=1, Criteria1:=Array("APPLE", "NAME"), Operator:=xlFilterValues
'// Have error handling in case if no data is found
On Error Resume Next
With rng
'// Use Offset and Resize to exclude header
Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'// If rows were found (i.e. there's no error), delete them
If Err = 0 Then rngVisible.EntireRow.Delete
On Error GoTo 0
'// Remove filter
ActiveSheet.AutoFilterMode = False
End Sub
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
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%2f53950383%2fdeleting-multiple-rows-based-on-a-set-criteria%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
1
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
IF Cells(i, 14).Value2 = "APPLE" OR Cells(i, 14).Value2 = "NAME" THEN Rows(i).Delete
Next i
answered Dec 27 '18 at 20:17
Cyril
2,4811822
add a comment |
1
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
IF Cells(i, 14).Value2 = "APPLE" OR Cells(i, 14).Value2 = "NAME" THEN Rows(i).Delete
Next i
answered Dec 27 '18 at 20:17
Cyril
2,4811822
add a comment |
1
1
1
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
IF Cells(i, 14).Value2 = "APPLE" OR Cells(i, 14).Value2 = "NAME" THEN Rows(i).Delete
Next i
answered Dec 27 '18 at 20:17
Cyril
2,4811822
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
IF Cells(i, 14).Value2 = "APPLE" OR Cells(i, 14).Value2 = "NAME" THEN Rows(i).Delete
Next i
answered Dec 27 '18 at 20:17
Cyril
2,4811822
edited Dec 27 '18 at 20:33
answered Dec 27 '18 at 20:17
Cyril
2,4811822
answered Dec 27 '18 at 20:17
Cyril
2,4811822
answered Dec 27 '18 at 20:17
Cyril
2,4811822
2,4811822
add a comment |
add a comment |
1
The fast way to delete rows is using AutoFilter:
Sub FastDelete()
Dim rng As Range, rngVisible As Range
'//Remove filter if any
ActiveSheet.AutoFilterMode = False
'// Get range of only one column (N)
Set rng = Range(Cells(1, 14), Cells(Rows.Count, 14).End(xlUp))
'// Field:=1 because filter has only one field
rng.AutoFilter Field:=1, Criteria1:=Array("APPLE", "NAME"), Operator:=xlFilterValues
'// Have error handling in case if no data is found
On Error Resume Next
With rng
'// Use Offset and Resize to exclude header
Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'// If rows were found (i.e. there's no error), delete them
If Err = 0 Then rngVisible.EntireRow.Delete
On Error GoTo 0
'// Remove filter
ActiveSheet.AutoFilterMode = False
End Sub
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
add a comment |
1
The fast way to delete rows is using AutoFilter:
Sub FastDelete()
Dim rng As Range, rngVisible As Range
'//Remove filter if any
ActiveSheet.AutoFilterMode = False
'// Get range of only one column (N)
Set rng = Range(Cells(1, 14), Cells(Rows.Count, 14).End(xlUp))
'// Field:=1 because filter has only one field
rng.AutoFilter Field:=1, Criteria1:=Array("APPLE", "NAME"), Operator:=xlFilterValues
'// Have error handling in case if no data is found
On Error Resume Next
With rng
'// Use Offset and Resize to exclude header
Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'// If rows were found (i.e. there's no error), delete them
If Err = 0 Then rngVisible.EntireRow.Delete
On Error GoTo 0
'// Remove filter
ActiveSheet.AutoFilterMode = False
End Sub
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
add a comment |
1
1
1
The fast way to delete rows is using AutoFilter:
Sub FastDelete()
Dim rng As Range, rngVisible As Range
'//Remove filter if any
ActiveSheet.AutoFilterMode = False
'// Get range of only one column (N)
Set rng = Range(Cells(1, 14), Cells(Rows.Count, 14).End(xlUp))
'// Field:=1 because filter has only one field
rng.AutoFilter Field:=1, Criteria1:=Array("APPLE", "NAME"), Operator:=xlFilterValues
'// Have error handling in case if no data is found
On Error Resume Next
With rng
'// Use Offset and Resize to exclude header
Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'// If rows were found (i.e. there's no error), delete them
If Err = 0 Then rngVisible.EntireRow.Delete
On Error GoTo 0
'// Remove filter
ActiveSheet.AutoFilterMode = False
End Sub
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
The fast way to delete rows is using AutoFilter:
Sub FastDelete()
Dim rng As Range, rngVisible As Range
'//Remove filter if any
ActiveSheet.AutoFilterMode = False
'// Get range of only one column (N)
Set rng = Range(Cells(1, 14), Cells(Rows.Count, 14).End(xlUp))
'// Field:=1 because filter has only one field
rng.AutoFilter Field:=1, Criteria1:=Array("APPLE", "NAME"), Operator:=xlFilterValues
'// Have error handling in case if no data is found
On Error Resume Next
With rng
'// Use Offset and Resize to exclude header
Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'// If rows were found (i.e. there's no error), delete them
If Err = 0 Then rngVisible.EntireRow.Delete
On Error GoTo 0
'// Remove filter
ActiveSheet.AutoFilterMode = False
End Sub
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
edited Dec 28 '18 at 18:42
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
answered Dec 27 '18 at 20:41
JohnyL
3,4561822
3,4561822
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
add a comment |
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
why are you using a filter? simpler method is search for the key word/s and then delete the line. The answer below is easier to understand and much simpler.
– alowflyingpig
Dec 27 '18 at 21:34
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
@alowflyingpig The lengthier code doesn't mean it's slower. Line-by-line is much slower than using filter. Do your tests before accusing.
– JohnyL
Dec 28 '18 at 5:01
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
never said it wasn't quicker/slower. I stated easier and simpler. A few lines for the end user is simpler and easier to read/understand. Um, before you accuse read what user write...
– alowflyingpig
Dec 29 '18 at 3:55
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
@alowflyingpig I understood you.
– JohnyL
Dec 29 '18 at 6:50
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53950383%2fdeleting-multiple-rows-based-on-a-set-criteria%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
Fastest method for deleting rows would be to use AutoFilter
– JohnyL
Dec 27 '18 at 20:29