Excel VBA speed up code to hide rows/delete color Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Cleaning up and reformatting imported data in an Excel sheetMatching between files and a list of filenames at scaleConsolidate used student hours into master sheet for exportIncreasing speed of Excel VBA code used for order reportMerging rows in excel using VBAShow all full/partial search matches in the first worksheet from other worksheetsDelete unmatched rows in ExcelHide Excel Rows fasterMacro that combines data from multiple worksheetsOpen/Close several workbooks to sort data
A term for a woman complaining about things/begging in a cute/childish way
How often does castling occur in grandmaster games?
What is the role of と after a noun when it doesn't appear to count or list anything?
Why not send Voyager 3 and 4 following up the paths taken by Voyager 1 and 2 to re-transmit signals of later as they fly away from Earth?
Why is std::move not [[nodiscard]] in C++20?
Caught masturbating at work
The test team as an enemy of development? And how can this be avoided?
Why are vacuum tubes still used in amateur radios?
Why do early math courses focus on the cross sections of a cone and not on other 3D objects?
Monty Hall Problem-Probability Paradox
What order were files/directories output in dir?
Asymptotics question
Simple HTTP Server
Project Euler #1 in C++
What does 丫 mean? 丫是什么意思？
Why is the change of basis formula counter-intuitive? [See details]
Is it possible for SQL statements to execute concurrently within a single session in SQL Server?
How do living politicians protect their readily obtainable signatures from misuse?
Does silver oxide react with hydrogen sulfide?
Co-worker has annoying ringtone
How much damage would a cupful of neutron star matter do to the Earth?
Does the Black Tentacles spell do damage twice at the start of turn to an already restrained creature?
Delete free apps from library
How does light 'choose' between wave and particle behaviour?
Excel VBA speed up code to hide rows/delete color
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?Cleaning up and reformatting imported data in an Excel sheetMatching between files and a list of filenames at scaleConsolidate used student hours into master sheet for exportIncreasing speed of Excel VBA code used for order reportMerging rows in excel using VBAShow all full/partial search matches in the first worksheet from other worksheetsDelete unmatched rows in ExcelHide Excel Rows fasterMacro that combines data from multiple worksheetsOpen/Close several workbooks to sort data
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
$begingroup$
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 '18 at 17:27
200_success
131k17157422
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
$endgroup$
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
$begingroup$
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 '18 at 17:27
200_success
131k17157422
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
$endgroup$
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
$begingroup$
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?
$endgroup$
– Mast
Nov 5 '18 at 10:34
$begingroup$
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.
$endgroup$
– user7202022
Nov 5 '18 at 13:02
1
$begingroup$
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.
$endgroup$
– Greedo
Nov 7 '18 at 10:18
add a comment |
$begingroup$
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 '18 at 17:27
200_success
131k17157422
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
$endgroup$
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 '18 at 17:27
200_success
131k17157422
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
edited Nov 6 '18 at 17:27
200_success
131k17157422
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
edited Nov 6 '18 at 17:27
200_success
131k17157422
edited Nov 6 '18 at 17:27
200_success
131k17157422
edited Nov 6 '18 at 17:27
200_success
131k17157422
131k17157422
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
asked Nov 5 '18 at 9:03
user7202022user7202022
1062
1062
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
$begingroup$
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?
$endgroup$
– Mast
Nov 5 '18 at 10:34
$begingroup$
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.
$endgroup$
– user7202022
Nov 5 '18 at 13:02
1
$begingroup$
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.
$endgroup$
– Greedo
Nov 7 '18 at 10:18
add a comment |
1
$begingroup$
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?
$endgroup$
– Mast
Nov 5 '18 at 10:34
$begingroup$
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.
$endgroup$
– user7202022
Nov 5 '18 at 13:02
1
$begingroup$
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.
$endgroup$
– Greedo
Nov 7 '18 at 10:18
1
1
$begingroup$
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?
$endgroup$
– Mast
Nov 5 '18 at 10:34
$begingroup$
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?
$endgroup$
– Mast
Nov 5 '18 at 10:34
$begingroup$
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.
$endgroup$
– user7202022
Nov 5 '18 at 13:02
$begingroup$
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.
$endgroup$
– user7202022
Nov 5 '18 at 13:02
1
1
$begingroup$
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.$endgroup$
– Greedo
Nov 7 '18 at 10:18
$begingroup$
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.$endgroup$
– Greedo
Nov 7 '18 at 10:18
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
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 '18 at 7:06
200_success
131k17157422
answered Nov 13 '18 at 0:20
Dan MDan M
12
$endgroup$
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: "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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
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
$begingroup$
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 '18 at 7:06
200_success
131k17157422
answered Nov 13 '18 at 0:20
Dan MDan M
12
$endgroup$
add a comment |
$begingroup$
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 '18 at 7:06
200_success
131k17157422
answered Nov 13 '18 at 0:20
Dan MDan M
12
$endgroup$
add a comment |
$begingroup$
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 '18 at 7:06
200_success
131k17157422
answered Nov 13 '18 at 0:20
Dan MDan M
12
$endgroup$
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 '18 at 7:06
200_success
131k17157422
answered Nov 13 '18 at 0:20
Dan MDan M
12
edited Nov 13 '18 at 7:06
200_success
131k17157422
edited Nov 13 '18 at 7:06
200_success
131k17157422
edited Nov 13 '18 at 7:06
200_success
131k17157422
131k17157422
answered Nov 13 '18 at 0:20
Dan MDan M
12
answered Nov 13 '18 at 0:20
Dan MDan M
12
answered Nov 13 '18 at 0:20
Dan MDan M
12
12
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
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');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
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');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
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');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
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
$begingroup$
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?
$endgroup$
– Mast
Nov 5 '18 at 10:34
$begingroup$
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.
$endgroup$
– user7202022
Nov 5 '18 at 13:02
1
$begingroup$
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.$endgroup$
– Greedo
Nov 7 '18 at 10:18