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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
VBA Code:
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,393
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 19, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,393
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad it worked for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,231
Members
412,372
Latest member
JON_ROCKS
Top