Removing =ROUND functions from workbook

theanchorman

New Member
Joined
Aug 24, 2012
Messages
31
Hello, I have a Excel file that has previously had ROUND formulas in many locations of the workbook. Almost 11k cells with ROUND formulas. Is there a VBA code I can use to remove all or most of them? I need to remove the beggining part of the formula "=ROUND(" and the end of it ",0)". Any suggestions?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hello, I have a Excel file that has previously had ROUND formulas in many locations of the workbook. Almost 11k cells with ROUND formulas. Is there a VBA code I can use to remove all or most of them? I need to remove the beggining part of the formula "=ROUND(" and the end of it ",0)". Any suggestions?
Post the formula or formulas you want to replace and for each the replacement you want to make.
 
Upvote 0
Hi to all.
This macro would probably do the job for you. It will run on all cells of the used range in the active sheet.
VBA Code:
Option Explicit
Sub test()
    Dim cel    As Range
    For Each cel In ActiveSheet.UsedRange
        If cel.HasFormula = True And Left(cel.Formula, 7) = "=ROUND(" Then cel.Formula = "=" & Mid(cel.Formula, 8, Len(cel.Formula) - 10)
    Next cel
End Sub
No testing for merged cells or locked cells or whatever.
 
Last edited:
Upvote 0
Post the formula or formulas you want to replace and for each the replacement you want to make.

This file has various ROUND formulas at listed below. Essentially, I just want to remove the ROUND from the entire formula at let the formula calculate as is.

=ROUND(A200/D200* F150,0)
=ROUND(A200*A150,0)
=ROUND((+A200+D200)*F150,0)
=ROUND((+A200+B200+D200)*F150,0)
=ROUND(SUM(A200:AD200)*F150+F180,0)
 
Upvote 0
Hi to all.
This macro would probably do the job for you. It will run on all cells of the used range in the active sheet.
VBA Code:
Option Explicit
Sub test()
    Dim cel    As Range
    For Each cel In ActiveSheet.UsedRange
        If cel.HasFormula = True And Left(cel.Formula, 7) = "=ROUND(" Then cel.Formula = "=" & Mid(cel.Formula, 8, Len(cel.Formula) - 10)
    Next cel
End Sub
No testing for merged cells or locked cells or whatever.
Thanks @rollis13! This helped a lot as I was able to get rid of 90% of the formulas, but the macro would give me an error at times as maybe some of the formulas are a bit different with ROUND functions in it. Any other suggesstions?
 
Upvote 0
Oh, that would need the use of function Find to search for a Round formula inside a bigger formula. Sorry, but this goes a little beyond my knowledge :cry:.
That's probably why @JoeMo asked about your used formulas but you didn't say anything about Round embedded in formulas :rolleyes:.
 
Upvote 0
You may want to create a script to print out out all of the formulas & Locations of each formula to allow you to see what the structure of each formula is. ;)
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top