1

I have a column which I formated in the following way:

Range("A:A").NumberFormat = "dd.mm.yyyy"

Now, I am looking for an efficient version for this:

    For k = 1 To Range("A1").End(xlDown).Row
    ' from first to last row
    Cells(k, 1).Value = CDate(Cells(k, 1).Value)
    Next k

This takes forever ;( Does someone know an efficient solution? Thanks!!!

Why do I need this? When running:

      With ws.Sort
        .SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1", Range("A1").End(xlDown))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I got strange sorting like:

01.01.2021
01.02.2021
01.03.2021
02.01.2021

Peer Breier
  • 361
  • 2
  • 13
  • 1
    Load the data into an array, process that, then write it back to the range. – Rory Apr 29 '21 at 10:10
  • Or use Data - Text to Columns on it. – Rory Apr 29 '21 at 10:21
  • I really don't get your idea of looping, why don't you copy/paste and then format column? What I'm missing here? – bankeris Apr 29 '21 at 10:52
  • I edited my question. I'm doing all this CDate conversion for sorting purposes. I have german dates but different computer default dates. So, I need to make sure, the format dd.mm.yyyy is always kept. CDate was solving that issue to perform sorting no matter which system. – Peer Breier Apr 29 '21 at 11:59
  • If the column you sorted **does not contain date** the sorting of strings returns correctly. Are you sure that the column contains date type values? – FaneDuru Apr 29 '21 at 12:19
  • I run: .NumberFormat = "dd.mm.yyyy" - Before, it is General type – Peer Breier Apr 29 '21 at 12:22

1 Answers1

1

Can CDate be used on a Range without a for loop?

Yes it is possible to convert your range without a loop but there is no CDATE formula in Excel so you have to use the formula Date() with RIGHT(), MID() and LEFT()

For example

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

enter image description here

Now to your question

Try this

Option Explicit

Sub Sample()
    Dim rng As Range
    Dim sAddr As String
    Dim ws As Worksheet
    Dim lRow As Long
    
    '~~> Change Sheet as applicable
    Set ws = Sheet1
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Set rng = .Range("A1:A" & lRow)
        sAddr = rng.Address

        rng = Evaluate("index(DATE(RIGHT(" & sAddr & _
                       ",4),MID(" & sAddr & ",4,2),LEFT(" & _
                       sAddr & ",2)),)")
        
        '~~> Change to whatever format you want
        rng.NumberFormat = "dd/mm/yyyy"
    End With
End Sub

For explanation see Convert an entire range to uppercase without looping through all the cells

In Action

enter image description here

Having said that, I would prefer what @Rory mentioned above. Use Data - Text to Columns.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks Siddharth. If I get it right, your code is swapping dd and mm, right? Actually, the format is already fine, I want to keep dd.mm.yyyy . But when I use sorting on the formated column, it performs a strange sorting on dd because it is only recognized as number. Whereas, when I used the for-loop, everything is recognized just fine. – Peer Breier Apr 29 '21 at 11:54
  • `your code is swapping dd and mm, right?` no it doesnt. `Actually, the format is already fine, I want to keep dd.mm.yyyy` Well `dd.mm.yyyy` is not the correct "date" format. – Siddharth Rout Apr 29 '21 at 15:11
  • Ok, so correct would mean: ```mm.dd.yyyy``` ? Can I still treat the date```dd.mm.yyyy``` somehow? (for sorting) – Peer Breier Apr 29 '21 at 15:16
  • I will try to use your conversion to numbers for sorting and afterwards convert it back with ```.NumberFormat``` - If that works, that would do the trick. I also have another sheet, where the format is already Date, but sorting also doesn't work as I want it. How can I use your function on that? – Peer Breier Apr 29 '21 at 15:25
  • 1
    `Ok, so correct would mean: mm.dd.yyyy ?` No. I am referring to the ".". That is why I am using `rng.NumberFormat = "dd/mm/yyyy"` The above dates can be sorted in ascending or descending order. – Siddharth Rout Apr 29 '21 at 15:37
  • 1
    Thank you so much, it's finally working. I used this function to have the other file be converted to the underlying value ```.NumberFormat = "@"```. Thank you so much again!!!!! Really happy for your help!!!! – Peer Breier Apr 29 '21 at 16:28