ActiveSheet.Range AutoFilter RGB Is Slow
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have a macro in Excel VBA and one of the steps within it is performing an AutoFilter on the ActiveSheet range, filter based on color. This step seems quite time consuming and I am wondering if there might be a quicker way to filter my data? Is filtering on color usually slower? here's a sample of the code that I am using:
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AB$100000").AutoFilter Field:=1, Criteria1:=RGB(255 _
, 199, 206), Operator:=xlFilterCellColor
Rows("2:100000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba excel-vba filtering
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
add a comment |
0
I have a macro in Excel VBA and one of the steps within it is performing an AutoFilter on the ActiveSheet range, filter based on color. This step seems quite time consuming and I am wondering if there might be a quicker way to filter my data? Is filtering on color usually slower? here's a sample of the code that I am using:
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AB$100000").AutoFilter Field:=1, Criteria1:=RGB(255 _
, 199, 206), Operator:=xlFilterCellColor
Rows("2:100000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba excel-vba filtering
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
What does "quite time consuming" mean? Do you really need to go to row 100k?
– SJR
Nov 23 '18 at 16:31
@SJR, no, you have a point there. I did not originally write this code. I think the author was trying to make sure they captured all records in the macro. They data set I was running had approx 32K rows
– user10591679
Nov 23 '18 at 18:05
add a comment |
0
0
0
I have a macro in Excel VBA and one of the steps within it is performing an AutoFilter on the ActiveSheet range, filter based on color. This step seems quite time consuming and I am wondering if there might be a quicker way to filter my data? Is filtering on color usually slower? here's a sample of the code that I am using:
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AB$100000").AutoFilter Field:=1, Criteria1:=RGB(255 _
, 199, 206), Operator:=xlFilterCellColor
Rows("2:100000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba excel-vba filtering
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
I have a macro in Excel VBA and one of the steps within it is performing an AutoFilter on the ActiveSheet range, filter based on color. This step seems quite time consuming and I am wondering if there might be a quicker way to filter my data? Is filtering on color usually slower? here's a sample of the code that I am using:
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AB$100000").AutoFilter Field:=1, Criteria1:=RGB(255 _
, 199, 206), Operator:=xlFilterCellColor
Rows("2:100000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba excel-vba filtering
excel vba excel-vba filtering
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
edited Nov 26 '18 at 7:29
Pᴇʜ
25.2k63052
25.2k63052
asked Nov 23 '18 at 15:28
user10591679
What does "quite time consuming" mean? Do you really need to go to row 100k?
– SJR
Nov 23 '18 at 16:31
@SJR, no, you have a point there. I did not originally write this code. I think the author was trying to make sure they captured all records in the macro. They data set I was running had approx 32K rows
– user10591679
Nov 23 '18 at 18:05
add a comment |
What does "quite time consuming" mean? Do you really need to go to row 100k?
– SJR
Nov 23 '18 at 16:31
@SJR, no, you have a point there. I did not originally write this code. I think the author was trying to make sure they captured all records in the macro. They data set I was running had approx 32K rows
– user10591679
Nov 23 '18 at 18:05
What does "quite time consuming" mean? Do you really need to go to row 100k?
– SJR
Nov 23 '18 at 16:31
What does "quite time consuming" mean? Do you really need to go to row 100k?
– SJR
Nov 23 '18 at 16:31
@SJR, no, you have a point there. I did not originally write this code. I think the author was trying to make sure they captured all records in the macro. They data set I was running had approx 32K rows
– user10591679
Nov 23 '18 at 18:05
@SJR, no, you have a point there. I did not originally write this code. I think the author was trying to make sure they captured all records in the macro. They data set I was running had approx 32K rows
– user10591679
Nov 23 '18 at 18:05
add a comment |
2 Answers
2
active
oldest
votes
0
Can you try this. Define the last row of data and don't select. I'm not sure it will make a huge difference but see how it goes.
Sub Macro1()
Dim r As Long
r = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$AB$" & r)
.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete shift:=xlUp
End With
ActiveSheet.AutoFilterMode = False
End Sub
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
add a comment |
0
You don't need to select.
Sub DoIt()
Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
rng.Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = 0
End Sub
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
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%2f53449342%2factivesheet-range-autofilter-rgb-is-slow%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
Can you try this. Define the last row of data and don't select. I'm not sure it will make a huge difference but see how it goes.
Sub Macro1()
Dim r As Long
r = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$AB$" & r)
.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete shift:=xlUp
End With
ActiveSheet.AutoFilterMode = False
End Sub
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
add a comment |
0
Can you try this. Define the last row of data and don't select. I'm not sure it will make a huge difference but see how it goes.
Sub Macro1()
Dim r As Long
r = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$AB$" & r)
.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete shift:=xlUp
End With
ActiveSheet.AutoFilterMode = False
End Sub
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
add a comment |
0
0
0
Can you try this. Define the last row of data and don't select. I'm not sure it will make a huge difference but see how it goes.
Sub Macro1()
Dim r As Long
r = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$AB$" & r)
.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete shift:=xlUp
End With
ActiveSheet.AutoFilterMode = False
End Sub
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
Can you try this. Define the last row of data and don't select. I'm not sure it will make a huge difference but see how it goes.
Sub Macro1()
Dim r As Long
r = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$AB$" & r)
.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete shift:=xlUp
End With
ActiveSheet.AutoFilterMode = False
End Sub
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
answered Nov 23 '18 at 18:10
SJRSJR
13.6k31219
13.6k31219
add a comment |
add a comment |
0
You don't need to select.
Sub DoIt()
Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
rng.Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = 0
End Sub
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
add a comment |
0
You don't need to select.
Sub DoIt()
Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
rng.Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = 0
End Sub
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
add a comment |
0
0
0
You don't need to select.
Sub DoIt()
Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
rng.Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = 0
End Sub
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
You don't need to select.
Sub DoIt()
Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.AutoFilter Field:=1, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
rng.Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = 0
End Sub
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
answered Nov 23 '18 at 18:11
DavesexcelDavesexcel
5,17921937
5,17921937
add a comment |
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%2f53449342%2factivesheet-range-autofilter-rgb-is-slow%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
What does "quite time consuming" mean? Do you really need to go to row 100k?
– SJR
Nov 23 '18 at 16:31
@SJR, no, you have a point there. I did not originally write this code. I think the author was trying to make sure they captured all records in the macro. They data set I was running had approx 32K rows
– user10591679
Nov 23 '18 at 18:05