5

I have a list of start times that need to be converted to 24 hour format. I changed the format for the entire column, but i have to press Enter for every row for excel to apply the new format.

Is there a was i can force excel to evaluate the cell format and change it. I have few thousand rows.

enter image description here

1
  • I can't replicate the problem in Excel 2007. If I highlight the range and change the format, all highlighted entries are changed.
    – fixer1234
    Nov 17 '14 at 16:57
10

Yes, it bugs me too.

dim c as range
for each c in application.intersect(activesheet.usedrange, activesheet.columns("C"))
  c.value = c.value
next
2
  • that worked great!
    – vpets
    Jun 20 '11 at 1:30
  • 1
    you're right, none of the usual methods work. Jun 20 '11 at 23:43
2

If all your cells have some text in common, you can also do a find/replace - in your case find every ":", and replace it with a ":" (i.e. find a way to find/replace that does nothing), and excel will re-format the cell.

0
0

Another workaround is: a) Put value 1 in any unused cell on the sheet b) Copy the cell c) Select the range you wish to force-evaluate d) Past special - Values + Multiply

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy