Clear data not format with Macro

I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes.

Here is the Macro to clear the data.

Please Login or Register  to view this content.

One way is to add a formatting macro section after you clear the contents. Use the Record feature, start recording and set the formatting of the datasheet as you want to to look like (including formulas) and then stop the macro. Then go to the new module and copy that data and paste it in your other module just after you clear the contents of the page. This will clear your page and re-enter your formatting data. Be sure to remove any unnecessary lines of code where you clicked randomly or hit incorrect buttons.

A couple of things.

First, be sure to edit out the "selecting" from your recorded macros. The recorder records YOU selecting cells then doing something to them because that's what you did, but it's not necessary for macros per se. They can simply refer to the cells and action all in one step with no "selecting" in advance.

Second, there's a difference in VBA between .Clear and .ClearContents. You want to use the latter to keep formatting in the cell.

Please Login or Register  to view this content.