CODEWITHSUNDEEP
vba
Roca
Roca

Reputation: 69

Whole Column as text

I have an excel document which the first column is formated:

date
2017-08-01
2017-08-01
2017-08-01
2017-08-01
2017-08-01

What I do is to copy the whole column to a text document and paste it back to the worksheet. Tt fixes the date format on its own, it comes back, perfectly:

DATE
01/08/2017
01/08/2017
01/08/2017
01/08/2017
01/08/2017

I have tried formating the cells within excel (dd/mm/yyyy) and record macro, nothing works, only the cutting and pasting onto notepad, now I need to achieve this by vba, I tried the normal:

     Range("a2:a5").Select
 Selection.NumberFormat = "@"

But it did not work, any suggestions?

Upvotes: 0

Views: 42

Answers (2)

Slai
Slai

Reputation: 22876

In addition to the Evaluate methods for convertiong texts to number:

[a2:a6] = [--a2:a6]
[a2:a6].NumberFormat = "dd/mm/yyyy"

there is also the Excel method of copying blank cell and selecting Add in the Paste Special dialog:

[b1].Copy                                           ' copy blank cell
[a2:a6].PasteSpecial , xlPasteSpecialOperationAdd   ' and add it to the destination range
[a2:a6].NumberFormat = "dd/mm/yyyy"

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

If the dates are stored as text you can multiply them by 1 (or add 0) and then format. The calculation on the range lets Excel know it's dealing with numbers.

The code was taken from Multiply Entire Range By Value?

Sub Test()

    Dim rData As Range

    Set rData = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5")
    rData = Evaluate(rData.Address & "*1")
    rData.NumberFormat = "dd/mm/yyyy"

End Sub

Upvotes: 1

Related Questions