Excel VBA speed up code to hide rows/delete color
1
I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to process. Especially in situations when I have several sheets in one workbook and to apply this on each sheet = "printed page".
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
For Each cell In myRange
myRange.Interior.ColorIndex = 0
If cell.HasFormula = True And cell.Value = "" And cell.EntireRow.Hidden = False Then Rows(cell.Row).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
performance vba excel
edited Nov 6 at 17:27
200_success
128k15150412
asked Nov 5 at 9:03
user7202022
62
add a comment |
1
I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to process. Especially in situations when I have several sheets in one workbook and to apply this on each sheet = "printed page".
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
For Each cell In myRange
myRange.Interior.ColorIndex = 0
If cell.HasFormula = True And cell.Value = "" And cell.EntireRow.Hidden = False Then Rows(cell.Row).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
performance vba excel
edited Nov 6 at 17:27
200_success
128k15150412
asked Nov 5 at 9:03
user7202022
62
1
Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about?
– Mast
Nov 5 at 10:34
My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet.
– user7202022
Nov 5 at 13:02
1
The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. ThenUNIONthem all and only make one call to.Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once.
– Greedo
Nov 7 at 10:18
add a comment |
1
1
1
I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to process. Especially in situations when I have several sheets in one workbook and to apply this on each sheet = "printed page".
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
For Each cell In myRange
myRange.Interior.ColorIndex = 0
If cell.HasFormula = True And cell.Value = "" And cell.EntireRow.Hidden = False Then Rows(cell.Row).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
performance vba excel
edited Nov 6 at 17:27
200_success
128k15150412
asked Nov 5 at 9:03
user7202022
62
I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to process. Especially in situations when I have several sheets in one workbook and to apply this on each sheet = "printed page".
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
For Each cell In myRange
myRange.Interior.ColorIndex = 0
If cell.HasFormula = True And cell.Value = "" And cell.EntireRow.Hidden = False Then Rows(cell.Row).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
performance vba excel
performance vba excel
edited Nov 6 at 17:27
200_success
128k15150412
asked Nov 5 at 9:03
user7202022
62
edited Nov 6 at 17:27
200_success
128k15150412
asked Nov 5 at 9:03
user7202022
62
edited Nov 6 at 17:27
200_success
128k15150412
edited Nov 6 at 17:27
200_success
128k15150412
edited Nov 6 at 17:27
200_success
128k15150412
128k15150412
asked Nov 5 at 9:03
user7202022
62
asked Nov 5 at 9:03
user7202022
62
asked Nov 5 at 9:03
user7202022
62
62
1
Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about?
– Mast
Nov 5 at 10:34
My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet.
– user7202022
Nov 5 at 13:02
1
The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. ThenUNIONthem all and only make one call to.Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once.
– Greedo
Nov 7 at 10:18
add a comment |
1
Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about?
– Mast
Nov 5 at 10:34
My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet.
– user7202022
Nov 5 at 13:02
1
The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. ThenUNIONthem all and only make one call to.Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once.
– Greedo
Nov 7 at 10:18
1
1
Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about?
– Mast
Nov 5 at 10:34
Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about?
– Mast
Nov 5 at 10:34
My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet.
– user7202022
Nov 5 at 13:02
My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet.
– user7202022
Nov 5 at 13:02
1
1
The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. Then
UNION them all and only make one call to .Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once.– Greedo
Nov 7 at 10:18
The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. Then
UNION them all and only make one call to .Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once.– Greedo
Nov 7 at 10:18
add a comment |
1 Answer
1
active
oldest
votes
0
Your test for hidden rows suggests you may already have hidden rows.
Nested IF statements could reduce the number of tests to be done.
As you are removing color from all cells, do it before the loop:
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
myRange.Interior.ColorIndex = 0
For Each cell In myRange
if Not cell.EntireRow.Hidden then
' once hidden, no more cell values will be tested on the row
If cell.HasFormula = True And cell.Value = "" then
Rows(cell.Row).EntireRow.Hidden = True
endif
endif
Next
Application.ScreenUpdating = True
End Sub
edited Nov 13 at 7:06
200_success
128k15150412
answered Nov 13 at 0:20
Dan M
12
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fcodereview.stackexchange.com%2fquestions%2f206964%2fexcel-vba-speed-up-code-to-hide-rows-delete-color%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
0
Your test for hidden rows suggests you may already have hidden rows.
Nested IF statements could reduce the number of tests to be done.
As you are removing color from all cells, do it before the loop:
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
myRange.Interior.ColorIndex = 0
For Each cell In myRange
if Not cell.EntireRow.Hidden then
' once hidden, no more cell values will be tested on the row
If cell.HasFormula = True And cell.Value = "" then
Rows(cell.Row).EntireRow.Hidden = True
endif
endif
Next
Application.ScreenUpdating = True
End Sub
edited Nov 13 at 7:06
200_success
128k15150412
answered Nov 13 at 0:20
Dan M
12
add a comment |
0
Your test for hidden rows suggests you may already have hidden rows.
Nested IF statements could reduce the number of tests to be done.
As you are removing color from all cells, do it before the loop:
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
myRange.Interior.ColorIndex = 0
For Each cell In myRange
if Not cell.EntireRow.Hidden then
' once hidden, no more cell values will be tested on the row
If cell.HasFormula = True And cell.Value = "" then
Rows(cell.Row).EntireRow.Hidden = True
endif
endif
Next
Application.ScreenUpdating = True
End Sub
edited Nov 13 at 7:06
200_success
128k15150412
answered Nov 13 at 0:20
Dan M
12
add a comment |
0
0
0
Your test for hidden rows suggests you may already have hidden rows.
Nested IF statements could reduce the number of tests to be done.
As you are removing color from all cells, do it before the loop:
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
myRange.Interior.ColorIndex = 0
For Each cell In myRange
if Not cell.EntireRow.Hidden then
' once hidden, no more cell values will be tested on the row
If cell.HasFormula = True And cell.Value = "" then
Rows(cell.Row).EntireRow.Hidden = True
endif
endif
Next
Application.ScreenUpdating = True
End Sub
edited Nov 13 at 7:06
200_success
128k15150412
answered Nov 13 at 0:20
Dan M
12
Your test for hidden rows suggests you may already have hidden rows.
Nested IF statements could reduce the number of tests to be done.
As you are removing color from all cells, do it before the loop:
Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
myRange.Interior.ColorIndex = 0
For Each cell In myRange
if Not cell.EntireRow.Hidden then
' once hidden, no more cell values will be tested on the row
If cell.HasFormula = True And cell.Value = "" then
Rows(cell.Row).EntireRow.Hidden = True
endif
endif
Next
Application.ScreenUpdating = True
End Sub
edited Nov 13 at 7:06
200_success
128k15150412
answered Nov 13 at 0:20
Dan M
12
edited Nov 13 at 7:06
200_success
128k15150412
edited Nov 13 at 7:06
200_success
128k15150412
edited Nov 13 at 7:06
200_success
128k15150412
128k15150412
answered Nov 13 at 0:20
Dan M
12
answered Nov 13 at 0:20
Dan M
12
answered Nov 13 at 0:20
Dan M
12
12
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f206964%2fexcel-vba-speed-up-code-to-hide-rows-delete-color%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
1
Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about?
– Mast
Nov 5 at 10:34
My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet.
– user7202022
Nov 5 at 13:02
1
The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. Then
UNIONthem all and only make one call to.Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once.– Greedo
Nov 7 at 10:18