After filtering, how to select and remove visible rows in Excel?
0
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=7, Criteria1:="promo"
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=12, Criteria1:="="
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
I would like to select the visible rows after filtering. I read up some post which uses .SpecialCells(xlCellTypeVisible) but I not sure how to amend my existing code.
excel vba filter worksheet
edited Nov 20 at 10:14
Amessihel
1,9631723
asked Nov 20 at 9:54
chee seng ng
248
add a comment |
0
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=7, Criteria1:="promo"
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=12, Criteria1:="="
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
I would like to select the visible rows after filtering. I read up some post which uses .SpecialCells(xlCellTypeVisible) but I not sure how to amend my existing code.
excel vba filter worksheet
edited Nov 20 at 10:14
Amessihel
1,9631723
asked Nov 20 at 9:54
chee seng ng
248
add a comment |
0
0
0
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=7, Criteria1:="promo"
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=12, Criteria1:="="
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
I would like to select the visible rows after filtering. I read up some post which uses .SpecialCells(xlCellTypeVisible) but I not sure how to amend my existing code.
excel vba filter worksheet
edited Nov 20 at 10:14
Amessihel
1,9631723
asked Nov 20 at 9:54
chee seng ng
248
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=7, Criteria1:="promo"
ActiveSheet.Range("$A$1:$O$1464").AutoFilter Field:=12, Criteria1:="="
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
I would like to select the visible rows after filtering. I read up some post which uses .SpecialCells(xlCellTypeVisible) but I not sure how to amend my existing code.
excel vba filter worksheet
excel vba filter worksheet
edited Nov 20 at 10:14
Amessihel
1,9631723
asked Nov 20 at 9:54
chee seng ng
248
edited Nov 20 at 10:14
Amessihel
1,9631723
asked Nov 20 at 9:54
chee seng ng
248
edited Nov 20 at 10:14
Amessihel
1,9631723
edited Nov 20 at 10:14
Amessihel
1,9631723
edited Nov 20 at 10:14
Amessihel
1,9631723
1,9631723
asked Nov 20 at 9:54
chee seng ng
248
asked Nov 20 at 9:54
chee seng ng
248
asked Nov 20 at 9:54
chee seng ng
248
248
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
You can do it this way (after filtering):
' Update: don't select the header
Activesheet.Range("$A$2:$O$1464").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
answered Nov 20 at 10:26
Amessihel
1,9631723
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
add a comment |
-1
'Put after this to your filter code
If Application.WorksheetFunction.Subtotal(3, Range("A1:A1464")) > 1 then
Range("$A$2:$A$1464").EntireRow.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
else
Msgbox "Criteria not found"
end if
answered Nov 20 at 11:06
Manoj Babu
312
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
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%2f53390365%2fafter-filtering-how-to-select-and-remove-visible-rows-in-excel%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
0
You can do it this way (after filtering):
' Update: don't select the header
Activesheet.Range("$A$2:$O$1464").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
answered Nov 20 at 10:26
Amessihel
1,9631723
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
add a comment |
0
You can do it this way (after filtering):
' Update: don't select the header
Activesheet.Range("$A$2:$O$1464").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
answered Nov 20 at 10:26
Amessihel
1,9631723
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
add a comment |
0
0
0
You can do it this way (after filtering):
' Update: don't select the header
Activesheet.Range("$A$2:$O$1464").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
answered Nov 20 at 10:26
Amessihel
1,9631723
You can do it this way (after filtering):
' Update: don't select the header
Activesheet.Range("$A$2:$O$1464").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
answered Nov 20 at 10:26
Amessihel
1,9631723
edited Nov 20 at 11:10
answered Nov 20 at 10:26
Amessihel
1,9631723
answered Nov 20 at 10:26
Amessihel
1,9631723
answered Nov 20 at 10:26
Amessihel
1,9631723
1,9631723
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
add a comment |
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
I tried replacing my last 2 line with your code. It shows compile error: Expected: list separator or
– chee seng ng
Nov 20 at 10:42
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
It kinda work but my column header got delete as well. Anyway to delete 2nd row onwards?
– chee seng ng
Nov 20 at 11:05
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
Yes it work perfectly! Thank you for your help.
– chee seng ng
Nov 22 at 4:11
add a comment |
-1
'Put after this to your filter code
If Application.WorksheetFunction.Subtotal(3, Range("A1:A1464")) > 1 then
Range("$A$2:$A$1464").EntireRow.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
else
Msgbox "Criteria not found"
end if
answered Nov 20 at 11:06
Manoj Babu
312
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
add a comment |
-1
'Put after this to your filter code
If Application.WorksheetFunction.Subtotal(3, Range("A1:A1464")) > 1 then
Range("$A$2:$A$1464").EntireRow.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
else
Msgbox "Criteria not found"
end if
answered Nov 20 at 11:06
Manoj Babu
312
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
add a comment |
-1
-1
-1
'Put after this to your filter code
If Application.WorksheetFunction.Subtotal(3, Range("A1:A1464")) > 1 then
Range("$A$2:$A$1464").EntireRow.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
else
Msgbox "Criteria not found"
end if
answered Nov 20 at 11:06
Manoj Babu
312
'Put after this to your filter code
If Application.WorksheetFunction.Subtotal(3, Range("A1:A1464")) > 1 then
Range("$A$2:$A$1464").EntireRow.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
else
Msgbox "Criteria not found"
end if
answered Nov 20 at 11:06
Manoj Babu
312
answered Nov 20 at 11:06
Manoj Babu
312
answered Nov 20 at 11:06
Manoj Babu
312
answered Nov 20 at 11:06
Manoj Babu
312
312
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
add a comment |
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review
– Nick
Nov 21 at 3:11
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%2f53390365%2fafter-filtering-how-to-select-and-remove-visible-rows-in-excel%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
