Mixing
How can I make my code faster? (Copying columns)
0
I'm new to vba and wrote the code below to match and copy columns; I'm trying to figure out how to make it more efficient as it takes some time to churn out the output. Any advice or feedback would be much appreciated! I thought of switching screen updating to false but it doesn't really speed things up. Could it be slow because I am using the autofilter method of filtering cells? Thank you in advance.
Sub GetItems()
Dim cel As Range
Dim celAddress As Range
Dim n As Integer
Dim SelRange As Range
Dim BrandsPasteLoc As Range
n = 1
'Application.ScreenUpdating = False
Worksheets("CA").Activate
ActiveSheet.Range("$A$1:$L$2622").AutoFilter Field:=10, Criteria1:="<>#N/A",
Criteria2:="<>0", Operator:=xlFilterValues
Worksheets("Items to push to CA").Activate
For Each cel In Range("A1:O1")
Brand = cel.Value
If Brand = "" Then
Resume Next
End If
Worksheets("CA").Activate
Application.Workbooks("Items Suggestion_CA").Worksheets("MY").Range("$A$1:$M$3959").AutoFilter Field:=5, Criteria1:=Brand, Operator:=xlFilterValues
Range("B:B").Sort _
Key1:=Range("b1"), Order1:=xlDescending 'sorts on Quantity
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Items to push to CA").Select
Set SelRange = ActiveSheet.Columns(n)
Set BrandsPasteLoc = Range(Range("A1:Z1").Find(Brand).Offset(1), Range("A1:Z1").Find(Brand).Offset(1).End(xlDown))
BrandsPasteLoc.Select
ActiveSheet.Paste
Worksheets(“CA”).Activate
n = n + 1
Next cel
Worksheets("Items to push to CA").Rows(2).Delete
Worksheets("Items to push to CA").Activate
Columns("A:O").Select
Selection.EntireColumn.AutoFit
Worksheets("Items to push to CA").Rows(60 & ":" &
Sheet1.Rows.Count).ClearContents
Worksheets("CA").Activate
Cells.AutoFilter
Worksheets("Items to push to CA").Activate
'Application.ScreenUpdating = True
End Sub
excel vba excel-vba
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
asked Nov 20 '18 at 9:45
Darck28Darck28
13
add a comment |
0
I'm new to vba and wrote the code below to match and copy columns; I'm trying to figure out how to make it more efficient as it takes some time to churn out the output. Any advice or feedback would be much appreciated! I thought of switching screen updating to false but it doesn't really speed things up. Could it be slow because I am using the autofilter method of filtering cells? Thank you in advance.
Sub GetItems()
Dim cel As Range
Dim celAddress As Range
Dim n As Integer
Dim SelRange As Range
Dim BrandsPasteLoc As Range
n = 1
'Application.ScreenUpdating = False
Worksheets("CA").Activate
ActiveSheet.Range("$A$1:$L$2622").AutoFilter Field:=10, Criteria1:="<>#N/A",
Criteria2:="<>0", Operator:=xlFilterValues
Worksheets("Items to push to CA").Activate
For Each cel In Range("A1:O1")
Brand = cel.Value
If Brand = "" Then
Resume Next
End If
Worksheets("CA").Activate
Application.Workbooks("Items Suggestion_CA").Worksheets("MY").Range("$A$1:$M$3959").AutoFilter Field:=5, Criteria1:=Brand, Operator:=xlFilterValues
Range("B:B").Sort _
Key1:=Range("b1"), Order1:=xlDescending 'sorts on Quantity
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Items to push to CA").Select
Set SelRange = ActiveSheet.Columns(n)
Set BrandsPasteLoc = Range(Range("A1:Z1").Find(Brand).Offset(1), Range("A1:Z1").Find(Brand).Offset(1).End(xlDown))
BrandsPasteLoc.Select
ActiveSheet.Paste
Worksheets(“CA”).Activate
n = n + 1
Next cel
Worksheets("Items to push to CA").Rows(2).Delete
Worksheets("Items to push to CA").Activate
Columns("A:O").Select
Selection.EntireColumn.AutoFit
Worksheets("Items to push to CA").Rows(60 & ":" &
Sheet1.Rows.Count).ClearContents
Worksheets("CA").Activate
Cells.AutoFilter
Worksheets("Items to push to CA").Activate
'Application.ScreenUpdating = True
End Sub
excel vba excel-vba
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
asked Nov 20 '18 at 9:45
Darck28Darck28
13
3
First of all avoid using.Activate,.Selectand.Selection: How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 20 '18 at 9:47
4
This would better fit to Stack Exchange: Code Review
– Pᴇʜ
Nov 20 '18 at 9:48
Thanks for your feedback :)
– Darck28
Nov 21 '18 at 14:55
add a comment |
0
0
0
I'm new to vba and wrote the code below to match and copy columns; I'm trying to figure out how to make it more efficient as it takes some time to churn out the output. Any advice or feedback would be much appreciated! I thought of switching screen updating to false but it doesn't really speed things up. Could it be slow because I am using the autofilter method of filtering cells? Thank you in advance.
Sub GetItems()
Dim cel As Range
Dim celAddress As Range
Dim n As Integer
Dim SelRange As Range
Dim BrandsPasteLoc As Range
n = 1
'Application.ScreenUpdating = False
Worksheets("CA").Activate
ActiveSheet.Range("$A$1:$L$2622").AutoFilter Field:=10, Criteria1:="<>#N/A",
Criteria2:="<>0", Operator:=xlFilterValues
Worksheets("Items to push to CA").Activate
For Each cel In Range("A1:O1")
Brand = cel.Value
If Brand = "" Then
Resume Next
End If
Worksheets("CA").Activate
Application.Workbooks("Items Suggestion_CA").Worksheets("MY").Range("$A$1:$M$3959").AutoFilter Field:=5, Criteria1:=Brand, Operator:=xlFilterValues
Range("B:B").Sort _
Key1:=Range("b1"), Order1:=xlDescending 'sorts on Quantity
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Items to push to CA").Select
Set SelRange = ActiveSheet.Columns(n)
Set BrandsPasteLoc = Range(Range("A1:Z1").Find(Brand).Offset(1), Range("A1:Z1").Find(Brand).Offset(1).End(xlDown))
BrandsPasteLoc.Select
ActiveSheet.Paste
Worksheets(“CA”).Activate
n = n + 1
Next cel
Worksheets("Items to push to CA").Rows(2).Delete
Worksheets("Items to push to CA").Activate
Columns("A:O").Select
Selection.EntireColumn.AutoFit
Worksheets("Items to push to CA").Rows(60 & ":" &
Sheet1.Rows.Count).ClearContents
Worksheets("CA").Activate
Cells.AutoFilter
Worksheets("Items to push to CA").Activate
'Application.ScreenUpdating = True
End Sub
excel vba excel-vba
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
asked Nov 20 '18 at 9:45
Darck28Darck28
13
I'm new to vba and wrote the code below to match and copy columns; I'm trying to figure out how to make it more efficient as it takes some time to churn out the output. Any advice or feedback would be much appreciated! I thought of switching screen updating to false but it doesn't really speed things up. Could it be slow because I am using the autofilter method of filtering cells? Thank you in advance.
Sub GetItems()
Dim cel As Range
Dim celAddress As Range
Dim n As Integer
Dim SelRange As Range
Dim BrandsPasteLoc As Range
n = 1
'Application.ScreenUpdating = False
Worksheets("CA").Activate
ActiveSheet.Range("$A$1:$L$2622").AutoFilter Field:=10, Criteria1:="<>#N/A",
Criteria2:="<>0", Operator:=xlFilterValues
Worksheets("Items to push to CA").Activate
For Each cel In Range("A1:O1")
Brand = cel.Value
If Brand = "" Then
Resume Next
End If
Worksheets("CA").Activate
Application.Workbooks("Items Suggestion_CA").Worksheets("MY").Range("$A$1:$M$3959").AutoFilter Field:=5, Criteria1:=Brand, Operator:=xlFilterValues
Range("B:B").Sort _
Key1:=Range("b1"), Order1:=xlDescending 'sorts on Quantity
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Items to push to CA").Select
Set SelRange = ActiveSheet.Columns(n)
Set BrandsPasteLoc = Range(Range("A1:Z1").Find(Brand).Offset(1), Range("A1:Z1").Find(Brand).Offset(1).End(xlDown))
BrandsPasteLoc.Select
ActiveSheet.Paste
Worksheets(“CA”).Activate
n = n + 1
Next cel
Worksheets("Items to push to CA").Rows(2).Delete
Worksheets("Items to push to CA").Activate
Columns("A:O").Select
Selection.EntireColumn.AutoFit
Worksheets("Items to push to CA").Rows(60 & ":" &
Sheet1.Rows.Count).ClearContents
Worksheets("CA").Activate
Cells.AutoFilter
Worksheets("Items to push to CA").Activate
'Application.ScreenUpdating = True
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
asked Nov 20 '18 at 9:45
Darck28Darck28
13
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
asked Nov 20 '18 at 9:45
Darck28Darck28
13
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
edited Nov 20 '18 at 9:51
Pᴇʜ
21k42650
21k42650
asked Nov 20 '18 at 9:45
Darck28Darck28
13
asked Nov 20 '18 at 9:45
Darck28Darck28
13
asked Nov 20 '18 at 9:45
Darck28Darck28
13
13
3
First of all avoid using.Activate,.Selectand.Selection: How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 20 '18 at 9:47
4
This would better fit to Stack Exchange: Code Review
– Pᴇʜ
Nov 20 '18 at 9:48
Thanks for your feedback :)
– Darck28
Nov 21 '18 at 14:55
add a comment |
3
First of all avoid using.Activate,.Selectand.Selection: How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 20 '18 at 9:47
4
This would better fit to Stack Exchange: Code Review
– Pᴇʜ
Nov 20 '18 at 9:48
Thanks for your feedback :)
– Darck28
Nov 21 '18 at 14:55
3
3
First of all avoid using
.Activate, .Select and .Selection: How to avoid using Select in Excel VBA– Pᴇʜ
Nov 20 '18 at 9:47
First of all avoid using
.Activate, .Select and .Selection: How to avoid using Select in Excel VBA– Pᴇʜ
Nov 20 '18 at 9:47
4
4
This would better fit to Stack Exchange: Code Review
– Pᴇʜ
Nov 20 '18 at 9:48
This would better fit to Stack Exchange: Code Review
– Pᴇʜ
Nov 20 '18 at 9:48
Thanks for your feedback :)
– Darck28
Nov 21 '18 at 14:55
Thanks for your feedback :)
– Darck28
Nov 21 '18 at 14:55
add a comment |
1 Answer
1
active
oldest
votes
-1
This question is more for Code Review but try adding Application.ScreenUpdating=False when the sub starts and Application.ScreenUpdating=True when tue sub ends
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
2
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
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%2f53390174%2fhow-can-i-make-my-code-faster-copying-columns%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
-1
This question is more for Code Review but try adding Application.ScreenUpdating=False when the sub starts and Application.ScreenUpdating=True when tue sub ends
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
2
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
add a comment |
-1
This question is more for Code Review but try adding Application.ScreenUpdating=False when the sub starts and Application.ScreenUpdating=True when tue sub ends
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
2
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
add a comment |
-1
-1
-1
This question is more for Code Review but try adding Application.ScreenUpdating=False when the sub starts and Application.ScreenUpdating=True when tue sub ends
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
This question is more for Code Review but try adding Application.ScreenUpdating=False when the sub starts and Application.ScreenUpdating=True when tue sub ends
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
answered Nov 20 '18 at 9:47
Foxfire And Burns And BurnsFoxfire And Burns And Burns
1,8431416
1,8431416
2
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
add a comment |
2
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
2
2
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
I didn't downvote, but the OP refers to screen updating not making any difference, oddly.
– SJR
Nov 20 '18 at 13:29
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
screen updating to false but it doesn't really speed things up Absolutely right. I did not read the question properly. My bad.
– Foxfire And Burns And Burns
Nov 20 '18 at 14:39
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%2f53390174%2fhow-can-i-make-my-code-faster-copying-columns%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
3
First of all avoid using
.Activate,.Selectand.Selection: How to avoid using Select in Excel VBA– Pᴇʜ
Nov 20 '18 at 9:47
4
This would better fit to Stack Exchange: Code Review
– Pᴇʜ
Nov 20 '18 at 9:48
Thanks for your feedback :)
– Darck28
Nov 21 '18 at 14:55