Running same VBA code for a range of columns (one column at a time)

Question

I have a range of dates which I need to convert to 'MM/DD/YYYY format (but as text) every month.

I used to manually convert these by using this formula =TEXT(Cell Ref.,"MM/DD/YYYY"). See picture above. I have recently started using below VBA code to save my time (there are around 18 columns with 200K rows worth of data every month).

Sub MM_DD_YYYY()
Application.ScreenUpdating = False
Dim rng As Range

Selection.NumberFormat = "0"

For Each rng In Selection
rng.Value = "+text(" & rng.Value & ",""MM/DD/YYYY"")"
Next rng

    Selection.TextToColumns DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Application.ScreenUpdating = True
End Sub

This code works fine if I select one column but fails if I select multiple columns because it has text to column element (which obviously only works for one column at a time). Is it possible to run the code one column at a time after selecting entire range without breaking it?

By the way, I have tried below alternatives of text to column:

1. Simulating F2+Enter. This works but takes a lot of time.

For Each rng In Selection
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
Next

2. Doesn't work for some reason.

Selection.Value = Selection.FormulaR1C1

3. Doesn't work for some reason.

For Each rng In Selection
Selection.Value = Selection.Value
Next rng

I would really appreciate your help or suggestion here. Thanks.

Answer 1

The output has a apostrophe at the beginning i.e. it's a text. That is why I was using text formula. Selection.NumberFormat = "MM/DD/YYYY" also doesn't work. range of dates are actual dates but output should be a text. – ram singh 12 secs ago

Try this. For explanation see Convert an entire range to uppercase without looping through all the cells. The below code uses INDEX() and TEXT().

Option Explicit

Sub Sample()
    Dim rng As Range
    Dim sAddr As String

    Set rng = Range("A1:C5") '<~~ Change this to relevant range
    sAddr = rng.Address

    rng = Evaluate("index(""'"" & Text(" & sAddr & ",""MM/DD/YYYY""),)")
End Sub

BEFORE:

AFTER:

EDIT

@SiddharthRout Just curious, is it possible to make it to work for more than one range. Example, I have dates in Col A and Col C (Col B has some other data). Current code doesn't work because if I select only Col A and Col C, they are now 2 ranges. Any thoughts? – ram singh 15 mins ago

Is this what you want?

Option Explicit

Sub Sample()
    Dim rng As Range
    Dim ar As Range
    Dim sAddr As String

    Set rng = Range("A1:A5,C1:C5") '<~~ Sample range
    
    For Each ar In rng.Areas
        sAddr = ar.Address

        ar = Evaluate("index(""'"" & Text(" & sAddr & ",""MM/DD/YYYY""),)")
    Next ar
End Sub

BEFORE:

AFTER: