Rounding Decimals 2 places

Is there a faster way to round decimals to 2 decimal places?

I am currently using the code below and works just fine; however, with the amount of data I work with I could have over 50,000+ rows of data and some values may not need rounding. My overall goal is to really identify only those values that exist where there are more than 2 number past the decimal and round it so that it doesn't take so long to run. With the amount of data I am using it is taking several minutes using the code below. I have attached an example. Any help is greatly appreciated. If identifying only those that need rounding isn't possible, any updates or faster way of doing this would help

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Does it have to be VBA? I know there is a "performance penalty" whenever VBA has to talk to Excel. The fastest approach I could readily see would be to use Excel's =ROUND(D2,2) function in a helper column. You could then, if needed, copy - paste special - as values over the original data, if needed.

Here's an alternative you can try cautiously.

Please Login or Register  to view this content.

The reason for caution is that it will round every constant value (not formula results) in the workbook to the value that's displayed. The setting gets turned off, but the change will be permament for the cells affected when the macro runs.

Hi matrix

You might try this...

Please Login or Register  to view this content.

Hi matrix

Just guessing...try this

Please Login or Register  to view this content.

0.109 sec (50K rows):

Please Login or Register  to view this content.

You're welcome

ProtonLeah
For some reason when it gets to row 44753, everything from this row forward is showing up as #N/A instead of rounding. Everything from 44752 prior is working fine. The Total rows for this particular file is 110228 rows (again that number could be more / less depending on the client I am working with).

I was trying to upload the actual file but it is 4MB. Below is the code. The only thing I had changed was the Sheets Name. I changed it back to Sheet1 as you had it, but still did same thing.

Please Login or Register  to view this content.

datasample.jpg

Thanks

Post a workbook with several (12...) of the #N/A rows