Macro question

[cajste]

I have a big range of data where where some of the cells have the wrong format when I get the it and calculations will not work because of that. If I selcet a cell, pushes F2 and then Enter the format will be corrected. However there are 20 000 rows and this needs to be done for 4 cells on each row so it is not doable.

What would a macro that selects a cell, pushes F2 and Enter, selects the next cell on the row, does the same thing and loops through all rows look like?

The first row shows the data as it looks when I get it and the second the way it should look.

Col1	Col2	Col3	Col4	Col6	Col7
0:11:52	0:12:09	TRUE	0:03:57	04 jan 2016 9:46:54	0:17:20
00:11:52	00:12:09	TRUE	00:03:57	2016-01-04 09:46	00:17:20

<tbody>
</tbody>

//Caj

 

[Peter_SSs]

See if this does it. Test on a copy.

Sub FixIt()
  With ActiveSheet.UsedRange
    Union(.Columns("A:B"), .Columns("D"), .Columns("F")).NumberFormat = "hh:mm:ss"
    .Columns("E").NumberFormat = "yyyy-mm-dd hh:mm"
    .Value = .Value
  End With
End Sub

 

[cajste]

Thanks, I'll try it!