Styjun

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;

1

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

share|improve this question

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 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)*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
  

  
  
  
  

 | 
show 1 more comment

1

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

share|improve this question

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 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)*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
  

  
  
  
  

 | 
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

share|improve this question

edited Mar 24 at 7:00

MarredCheese

3,51622642

asked Mar 24 at 5:37

Leo HernandezLeo Hernandez

63

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

share|improve this question

edited Mar 24 at 7:00

MarredCheese

3,51622642

asked Mar 24 at 5:37

Leo HernandezLeo Hernandez

63

share|improve this question

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

asked Mar 24 at 5:37

Leo HernandezLeo Hernandez

63

asked Mar 24 at 5:37

Leo HernandezLeo Hernandez

63

Range("v11:ay9000").numberformat = "0%;-0%;;"

Range("v11:ay9000").numberformat = "0%;-0%;;"

Range("v11:ay9000").numberformat = "0%;-0%;;"

Range("v11:ay9000").numberformat = "0%;-0%;;"

Range("v11:ay9000").numberformat = "0%;-0%;;"