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

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top