Refresh Data in Excel Spreadsheet

[Guest]

How do I refresh data in MS-Excel 2003 w/o manually pressing F2 and enter?

The issue is I have a coulnmn that was brought in from an external data
source as text format, when the column should have been accounting format or
currency format. When I re-format the column to accounting or currency, the
amounts are not refreshed to reflect the new format until I manually touch
the cell by pressing F2 and enter. This would be OK, except that I have 2500
rows of information to refresh. Is there a way to have the column
automatically refresh?

 

[Guest]

Hello Muzickdoc,

I assume the data has the little tag in the corner to indicate a formatting
issue?

If so, select all the data, click the first cell then shift control down
arrow. You should see a yellow box appear to the left of the list. In it is a
conversion from text to numbers option.

That should do it.

Good luck

 

[Dave O]

What's up, Doc?

This happens to me often enough that I wrote some code to handle it.
Highlight the range of data, then run this code. Enjoy the weekend!

Sub Selected_Range_Format()
Dim rCell As Range
Dim TrueVal As Variant

For Each rCell In Selection.Cells
TrueVal = Trim(rCell.Value)
rCell.ClearContents
rCell.NumberFormat = "$#,##0.00"
rCell.Value = TrueVal
Next rCell
End Sub