Excel VBA Clear formats when value is 0How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?Is there a way to crack the password on an Excel VBA Project?Show Excel 2007 Ribbon in XLS file using Excel VBAHow to avoid using Select in Excel VBAConditional Formatting using Excel VBA codeExcel VBA - read cell value from codeVBA Excel: Apply conditional formatting to BLANK cellsexcel replace function in access vbaHide Selected Columns in Excel using (VBA) Click ButtonExcel VBA macro to send emails to unique users in range
Using credit/debit card details vs swiping a card in a payment (credit card) terminal
Does the unit of measure matter when you are solving for the diameter of a circumference?
Adding spaces to string based on list
Image processing: Removal of two spots in fundus images
Is it possible to play as a necromancer skeleton?
Why are C64 games inconsistent with which joystick port they use?
Count Even Digits In Number
Is CD audio quality good enough?
What kind of metaphor is "trees in the wind"?
Is the Indo-European language family made up?
What is the object moving across the ceiling in this stock footage?
Where have Brexit voters gone?
Make 24 using exactly three 3s
How to pull out the underlying query syntax being used by dataset?
Are these reasonable traits for someone with autism?
Employer demanding to see degree after poor code review
Boss wants me to falsify a report. How should I document this unethical demand?
Simple function that simulates survey results based on sample size and probability
Why do they consider the Ori false gods?
Why do most published works in medical imaging try to reduce false positives?
Why did David Cameron offer a referendum on the European Union?
Why does a perfectly-identical repetition of a drawing command given within an earlier loop 𝘯𝘰𝘵 produce exactly the same line?
Pirate democracy at its finest
How to respond to an upset student?
Excel VBA Clear formats when value is 0
How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?Is there a way to crack the password on an Excel VBA Project?Show Excel 2007 Ribbon in XLS file using Excel VBAHow to avoid using Select in Excel VBAConditional Formatting using Excel VBA codeExcel VBA - read cell value from codeVBA Excel: Apply conditional formatting to BLANK cellsexcel replace function in access vbaHide Selected Columns in Excel using (VBA) Click ButtonExcel VBA macro to send emails to unique users in range
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have created a report in which there are some cells with null values. When my code applies the number format to null cells, it shows up as 0% for blank values. I added a code that then removes any values and format that show up as 0%, but it takes a really long time to run (30 sec or so) and sometimes crashes Excel.
Code:
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("v11:ay9000")
If cell = 0 Then cell.ClearFormats
Next cell
Application.ScreenUpdating = True
I would appreciate any help with the speed of the code.
excel vba
edited Mar 24 at 7:00
MarredCheese
3,51622642
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
|
show 1 more comment
I have created a report in which there are some cells with null values. When my code applies the number format to null cells, it shows up as 0% for blank values. I added a code that then removes any values and format that show up as 0%, but it takes a really long time to run (30 sec or so) and sometimes crashes Excel.
Code:
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("v11:ay9000")
If cell = 0 Then cell.ClearFormats
Next cell
Application.ScreenUpdating = True
I would appreciate any help with the speed of the code.
excel vba
edited Mar 24 at 7:00
MarredCheese
3,51622642
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
The reason why it takes over 30 seconds is because you are cycling through a ton of cell columnsV:AY. 30 seconds is generous! lol. Is the main crux of your question the speed of your code or the implementation of your code?
– medicine_man
Mar 24 at 5:47
That code doesn't 'removes any values', it only clears formats. If the number format is your sole concern, set it to General. Are you looking for zeroes or blanks or both? They aren't the same thing.
– user11246173
Mar 24 at 5:55
@medicine_man The overall concern is the speed. If I need to go about another of implementing the code then I definitely am open to that. I just can't have the report taking 30 secs or even crashing. I am trying to make the report as user friendly for the end user.
– Leo Hernandez
Mar 24 at 5:57
You are trying to edit(9000-11)*30cells (269,670 cells). There may be a way to edit your code, or add something, to make it faster, but I would be pretty happy with 30 seconds to be honest with you.
– medicine_man
Mar 24 at 5:59
@user11246173 I am trying to clear the zeros. Initally when I run the the report there are cells that have null values. However, when i apply the format therange the null values get converted to 0% or $0. So therefore I am trying to get the cell to go back to being null/empty. There are multiple columns and I am applying different number formats to each of the columns.
– Leo Hernandez
Mar 24 at 6:03
|
show 1 more comment
I have created a report in which there are some cells with null values. When my code applies the number format to null cells, it shows up as 0% for blank values. I added a code that then removes any values and format that show up as 0%, but it takes a really long time to run (30 sec or so) and sometimes crashes Excel.
Code:
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("v11:ay9000")
If cell = 0 Then cell.ClearFormats
Next cell
Application.ScreenUpdating = True
I would appreciate any help with the speed of the code.
excel vba
edited Mar 24 at 7:00
MarredCheese
3,51622642
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
I have created a report in which there are some cells with null values. When my code applies the number format to null cells, it shows up as 0% for blank values. I added a code that then removes any values and format that show up as 0%, but it takes a really long time to run (30 sec or so) and sometimes crashes Excel.
Code:
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("v11:ay9000")
If cell = 0 Then cell.ClearFormats
Next cell
Application.ScreenUpdating = True
I would appreciate any help with the speed of the code.
excel vba
excel vba
edited Mar 24 at 7:00
MarredCheese
3,51622642
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
edited Mar 24 at 7:00
MarredCheese
3,51622642
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
edited Mar 24 at 7:00
MarredCheese
3,51622642
edited Mar 24 at 7:00
MarredCheese
3,51622642
edited Mar 24 at 7:00
MarredCheese
3,51622642
3,51622642
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
asked Mar 24 at 5:37
Leo HernandezLeo Hernandez
63
63
The reason why it takes over 30 seconds is because you are cycling through a ton of cell columnsV:AY. 30 seconds is generous! lol. Is the main crux of your question the speed of your code or the implementation of your code?
– medicine_man
Mar 24 at 5:47
That code doesn't 'removes any values', it only clears formats. If the number format is your sole concern, set it to General. Are you looking for zeroes or blanks or both? They aren't the same thing.
– user11246173
Mar 24 at 5:55
@medicine_man The overall concern is the speed. If I need to go about another of implementing the code then I definitely am open to that. I just can't have the report taking 30 secs or even crashing. I am trying to make the report as user friendly for the end user.
– Leo Hernandez
Mar 24 at 5:57
You are trying to edit(9000-11)*30cells (269,670 cells). There may be a way to edit your code, or add something, to make it faster, but I would be pretty happy with 30 seconds to be honest with you.
– medicine_man
Mar 24 at 5:59
@user11246173 I am trying to clear the zeros. Initally when I run the the report there are cells that have null values. However, when i apply the format therange the null values get converted to 0% or $0. So therefore I am trying to get the cell to go back to being null/empty. There are multiple columns and I am applying different number formats to each of the columns.
– Leo Hernandez
Mar 24 at 6:03
|
show 1 more comment
The reason why it takes over 30 seconds is because you are cycling through a ton of cell columnsV:AY. 30 seconds is generous! lol. Is the main crux of your question the speed of your code or the implementation of your code?
– medicine_man
Mar 24 at 5:47
That code doesn't 'removes any values', it only clears formats. If the number format is your sole concern, set it to General. Are you looking for zeroes or blanks or both? They aren't the same thing.
– user11246173
Mar 24 at 5:55
@medicine_man The overall concern is the speed. If I need to go about another of implementing the code then I definitely am open to that. I just can't have the report taking 30 secs or even crashing. I am trying to make the report as user friendly for the end user.
– Leo Hernandez
Mar 24 at 5:57
You are trying to edit(9000-11)*30cells (269,670 cells). There may be a way to edit your code, or add something, to make it faster, but I would be pretty happy with 30 seconds to be honest with you.
– medicine_man
Mar 24 at 5:59
@user11246173 I am trying to clear the zeros. Initally when I run the the report there are cells that have null values. However, when i apply the format therange the null values get converted to 0% or $0. So therefore I am trying to get the cell to go back to being null/empty. There are multiple columns and I am applying different number formats to each of the columns.
– Leo Hernandez
Mar 24 at 6:03
The reason why it takes over 30 seconds is because you are cycling through a ton of cell columns
V:AY. 30 seconds is generous! lol. Is the main crux of your question the speed of your code or the implementation of your code?– medicine_man
Mar 24 at 5:47
The reason why it takes over 30 seconds is because you are cycling through a ton of cell columns
V:AY. 30 seconds is generous! lol. Is the main crux of your question the speed of your code or the implementation of your code?– medicine_man
Mar 24 at 5:47
That code doesn't 'removes any values', it only clears formats. If the number format is your sole concern, set it to General. Are you looking for zeroes or blanks or both? They aren't the same thing.
– user11246173
Mar 24 at 5:55
That code doesn't 'removes any values', it only clears formats. If the number format is your sole concern, set it to General. Are you looking for zeroes or blanks or both? They aren't the same thing.
– user11246173
Mar 24 at 5:55
@medicine_man The overall concern is the speed. If I need to go about another of implementing the code then I definitely am open to that. I just can't have the report taking 30 secs or even crashing. I am trying to make the report as user friendly for the end user.
– Leo Hernandez
Mar 24 at 5:57
@medicine_man The overall concern is the speed. If I need to go about another of implementing the code then I definitely am open to that. I just can't have the report taking 30 secs or even crashing. I am trying to make the report as user friendly for the end user.
– Leo Hernandez
Mar 24 at 5:57
You are trying to edit
(9000-11)*30 cells (269,670 cells). There may be a way to edit your code, or add something, to make it faster, but I would be pretty happy with 30 seconds to be honest with you.– medicine_man
Mar 24 at 5:59
You are trying to edit
(9000-11)*30 cells (269,670 cells). There may be a way to edit your code, or add something, to make it faster, but I would be pretty happy with 30 seconds to be honest with you.– medicine_man
Mar 24 at 5:59
@user11246173 I am trying to clear the zeros. Initally when I run the the report there are cells that have null values. However, when i apply the format therange the null values get converted to 0% or $0. So therefore I am trying to get the cell to go back to being null/empty. There are multiple columns and I am applying different number formats to each of the columns.
– Leo Hernandez
Mar 24 at 6:03
@user11246173 I am trying to clear the zeros. Initally when I run the the report there are cells that have null values. However, when i apply the format therange the null values get converted to 0% or $0. So therefore I am trying to get the cell to go back to being null/empty. There are multiple columns and I am applying different number formats to each of the columns.
– Leo Hernandez
Mar 24 at 6:03
|
show 1 more comment
1 Answer
1
active
oldest
votes
You have an XY problem.
You're trying to improve the time it takes to repair your initial problem. Instead, you should just set the correct number format in the first place.
Range("v11:ay9000").numberformat = "0%;-0%;;"
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
2
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
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
);
);
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%2f55321031%2fexcel-vba-clear-formats-when-value-is-0%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
You have an XY problem.
You're trying to improve the time it takes to repair your initial problem. Instead, you should just set the correct number format in the first place.
Range("v11:ay9000").numberformat = "0%;-0%;;"
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
2
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
add a comment |
You have an XY problem.
You're trying to improve the time it takes to repair your initial problem. Instead, you should just set the correct number format in the first place.
Range("v11:ay9000").numberformat = "0%;-0%;;"
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
2
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
add a comment |
You have an XY problem.
You're trying to improve the time it takes to repair your initial problem. Instead, you should just set the correct number format in the first place.
Range("v11:ay9000").numberformat = "0%;-0%;;"
You have an XY problem.
You're trying to improve the time it takes to repair your initial problem. Instead, you should just set the correct number format in the first place.
Range("v11:ay9000").numberformat = "0%;-0%;;"
edited Mar 24 at 6:51
answered Mar 24 at 5:58
user11246173
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
2
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
add a comment |
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
2
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
Could you please explain the meaning of "0%;-0%;;". VBA Help wasn't helpful.
– VBasic2008
Mar 24 at 7:38
2
2
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
A custom number format consists of four parts separated by 3 semi-colons; positive numbers, negative numbers, zeroes and text. By providing the format for positive and negative then intentionally omitting the zero and text format any zeroes or text will not be displayed. Look at an Accounting style format to see how zeroes can be converted to hyphens.
– user11246173
Mar 24 at 7:41
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
Review guidelines for customizing a number format
– user11246173
Mar 24 at 7:54
add a comment |
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.
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%2f55321031%2fexcel-vba-clear-formats-when-value-is-0%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
The reason why it takes over 30 seconds is because you are cycling through a ton of cell columns
V:AY. 30 seconds is generous! lol. Is the main crux of your question the speed of your code or the implementation of your code?– medicine_man
Mar 24 at 5:47
That code doesn't 'removes any values', it only clears formats. If the number format is your sole concern, set it to General. Are you looking for zeroes or blanks or both? They aren't the same thing.
– user11246173
Mar 24 at 5:55
@medicine_man The overall concern is the speed. If I need to go about another of implementing the code then I definitely am open to that. I just can't have the report taking 30 secs or even crashing. I am trying to make the report as user friendly for the end user.
– Leo Hernandez
Mar 24 at 5:57
You are trying to edit
(9000-11)*30cells (269,670 cells). There may be a way to edit your code, or add something, to make it faster, but I would be pretty happy with 30 seconds to be honest with you.– medicine_man
Mar 24 at 5:59
@user11246173 I am trying to clear the zeros. Initally when I run the the report there are cells that have null values. However, when i apply the format therange the null values get converted to 0% or $0. So therefore I am trying to get the cell to go back to being null/empty. There are multiple columns and I am applying different number formats to each of the columns.
– Leo Hernandez
Mar 24 at 6:03