Perform cell value modification without copy/paste - VBA

Question

So I have a connection source where it imports an XML file from a URL. The XML contains a few dates that are formatted in mm/dd/yy, however Excel doesn't seem to be able to tell that it is 20xx and instead asks me to specify whether it is 19xx or 20xx after each refresh I do to the data (the data gets updated daily).

So I made a script that fixes that issue using copy/paste. Problem is that it is slow and it can't be done in the background. If I run the script while I'm on a different worksheet it would start changing sheets really quickly and freeze for a few seconds. Here is my code below:

Sub test()

Dim listCols As ListColumns
Set listCols = Sheets("RawData").ListObjects("RawTable").ListColumns

'Sets the very last row & column to 0, to be copied later
Range("XFD1048576").Value = "0"

For col = 1 To listCols.Count 'Iterate through columns in table
    If listCols(col) = "DATECOL1" Or listCols(col) = "DATECOL2" Or listCols(col) = "DATECOL3" _
    Or listCols(col) = "DATECOL4" Or listCols(col) = "DATECOL5" Or listCols(col) = "RESERVATIONEND" Then

        For Each cell In listCols(col).DataBodyRange.Cells
            If cell.Value <> "" Then 'ignore empty cells
                'Copies the very last column & row
                With Range("XFD1048576")
                    .Copy
                End With
                'Pastes the '0' value from above and adds it to the original value in the cell it is pasting in
                With cell
                    .PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
                    .NumberFormat = "mm/dd/yy"
                End With
                Application.CutCopyMode = False
            End If
        Next
    End If
Next

Range("XFD1048576").ClearContents 'Clear the '0' in there

End Sub

Any help is appreciated.

EDIT:

EDIT2: I'm not sure what it is, but using the .value = .value definitey works. I tested it using a simple code as shown below:

Sub test3()
With Range("W1:W59")
    .Value = .Value
    .NumberFormat = "mm/dd/yy"
End With
End Sub

Answer 1

Her's a more efficient version of your code. It avoids the copy/paste operation, and the loop throught the cells

Sub Demo()
    Dim listCols As ListColumns
    Dim col As Long
    Dim cell As Range

    Set listCols = Sheets("RawData").ListObjects("RawTable").ListColumns

    FormatDates listCols("DATECOL1")
    FormatDates listCols("DATECOL2")
    FormatDates listCols("DATECOL3")
    FormatDates listCols("DATECOL4")
    FormatDates listCols("DATECOL5")
    FormatDates listCols("RESERVATIONEND")
End Sub

Private Sub FormatDates(ListCol As ListColumn)
    Dim rng As Range, arr As Range
    On Error Resume Next
    Set rng = ListCol.DataBodyRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    If Not rng Is Nothing Then
        For Each arr In rng.Areas
            With arr
                .NumberFormat = "mm/dd/yy"
                .Value = .Value
            End With
        Next
    End If
End Sub