Creating a Macro to edit cells

[ABCMIKE]

Hello, I'm having a nightmare...

I have a sheet with over 1000 times in cells. They are writes as HH:MM:SS:SS. I need them in HH:MM:SS format. I'm unable to change it in the Format Cells option. I was told a macro could do this, but when I've tried, it's just replacing the time in the cell with the time I recorded in the Macro. I'm losing information trying to get it right.

Can anyone help please?

Mike

 

[revcanon]

If you are unable to make the change via Format Cells by using Custom > h:mm:ss....

You could try something like this:

Sub test()
'
Range("A1:AZ106").NumberFormat = "h:mm:ss" 'you just need to select your cells via some selection
'or by columns
Columns("3:10").NumberFormat = "h:mm:ss"
'or by rows
Rows("2:40").NumberFormat = "h:mm:ss"
'
End Sub

You just have to figure out how you are going to select the cells you need to change.

However, if the custom number formatting doesn't work with "Format Cells", then I am not sure this will work either.

TEST on a BACKUP COPY

 

[ABCMIKE]

Thanks I'll give it a go. I hope it does work, but I'm not sure it will as it won't work with format cells pre macro.

As with excel, something sounds simple, but seldom is.

Thanks again

 

[revcanon]

If that doesn't work, you may need to upload a sample of the worksheet so we can try to understand why Format cells isn't working for it.

 

[ABCMIKE]

Oh ok, I didn't know I could do that. It's a really simple worksheet. There's not sensitive information on there, it's literally figures relating to figures. I will try your suggestion and if that doesn't work, maybe you could tell me where I upload my/a sheet?

Thanks