Removing hidden formatting - VBA substitute for double-click (Text to Columns not working?)

[dougmarkham]

Hi Folks,

I have an excel report that has been exported from a website. The date column has some hidden formatting on it, so that when I format the column using:

Range("RepTable[Delivery Date]").Select
Selection.NumberFormat = "yyyymmdd"

The date remains in the format: DD/MM/YYYY.

When I double click on any cell, the hidden date format is lost as expected, as excel registers that I have re-entered data.
In the past, I've gotten away with using VBA to run Text to Columns in order to simulate the double-clicking down a range of cells in a column e.g.

Range("RepTable[Delivery Date]").Select
Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

However, for some reason, today the code just has no effect. The options are set to automatic calculation so I am stuck, after a google search session.

Would anyone be able to help me find a more robust method (or suggest one) of simulating double-click in all cells in a column?

Kind regards,

Doug.

 

[Joe4]

It sounds to me that maybe you have dates that are being entered initially as Text.
If that is the case, then you should be able to use "Text to Columns", choosing the "Date" option in step 3 of the Wizard, and being sure to indicate the date structure that it is currently in (NOT the structure that you ultimately want it in).
So, if the dates are being listed like this "yyyymmdd", you would want to choose the "YMD" date option in step 3 (which tells it year comes first, followed by month, followed by day).
Then, after the data is correctly converted to valid dates, you can elect to format that column in any date format you desire.

If that is not what you are after, please post a sampling of what your data looks like, and an example of what you want it to look like when finished.

 

[dougmarkham]

It sounds to me that maybe you have dates that are being entered initially as Text.
If that is the case, then you should be able to use "Text to Columns", choosing the "Date" option in step 3 of the Wizard, and being sure to indicate the date structure that it is currently in (NOT the structure that you ultimately want it in).
So, if the dates are being listed like this "yyyymmdd", you would want to choose the "YMD" date option in step 3 (which tells it year comes first, followed by month, followed by day).
Then, after the data is correctly converted to valid dates, you can elect to format that column in any date format you desire.

If that is not what you are after, please post a sampling of what your data looks like, and an example of what you want it to look like when finished.

Hi Joe4,

Many thanks for replying, appreciate your time !
You are spot on: This works for me, thank you.
I'll take a better look at the third section of Text to Columns now.

Kind regards,
Doug.

 

[Joe4]

You are welcome.
Glad it worked for you.