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

  • 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
9

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
| improve this answer | |
  • that worked great! – vpets Jun 20 '11 at 1:30
  • 1
    you're right, none of the usual methods work. – Lance Roberts 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.

| improve this answer | |
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

| improve this answer | |

Your Answer

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