Reputation: 1167
Firstly, apologies if I make a lot of mistakes in best VBA practice. I've been learning the language for this project in particular, and there are probably a number of things I'm doing wrong, so sorry if I make anyone cringe.
Next, the problem. I'm trying to sort a range by date (held in one column), in exactly the way which the sort function on the tools menu works when sorting "anything that looks like a number as a number". The column is a mixture of UK-locale dates and text strings held in "general" formatted cells that are essentially just dates. In other words, something simple like:
Range(rngFirstCell, rngLastCell).Sort Key1:= 2, Order1:=xlAscending, _
DataOption1:=xlSortTextAsNumbers, Header:=xlYes
should do the trick. Indeed, with I think the only exception being that the recorded code uses xlGuess for Header, and includes a value for OrderCustom of 1, is exactly what the macro recorder produces. Needless to say, I've tried recorded code with the same results.
The problem is, instead of getting:
Type Date
gen 01/3/2008
date 02/4/2008
date 17/4/2008
gen 25/7/2009
I get:
Type Date
date 02/4/2008
date 17/4/2008
gen 01/3/2008
gen 25/7/2009
Since this works in later versions of Excel, I've concluded it's a bug in 2003. My current solution to which is to firstly set the NumberFormat property of all cells in the column to "d/m/yyyy", and then to iterate over them and replace each value with the result of CDate(Cell.Value). It makes the sort work. It also takes 10 seconds to reformat a column with 20 entries because there's so much interaction between the sheet and VBA (slow, from what I've read). Since it's entirely possible that some datasets I'll need to sort by code will be hundreds of cells long, I need something faster.
Can anyone suggest a better way of doing it?
For clarity, the code I'm using at the moment looks very like:
Range(rngFirstCell, rngLastCell).Columns(2).NumberFormat = "d/m/yyyy"
Dim intIndex As Long, varCellRef As Variant
For intIndex = 0 to Range(rngFirstCell, rngLastCell).Columns(2).End(xlDown).Row
Set varCellRef = Range(rngFirstCell, rngLastCell).Columns(2)(intIndex)
varCellRef.Value = CDate(varCellRef.Value)
Next
Range(rngFirstCell, rngLastCell).Sort Key1:= 2, Order1:=xlAscending, _
DataOption1:=xlSortTextAsNumbers, Header:=xlYes
Upvotes: 0
Views: 4893
Reputation: 53146
You are right to say referencing the sheet in a loop is slow, but it can be avoided by copying the data to a variant array and looping over that, then copy back to the sheet:
Dim rngFirstCell As Range
Dim rngLastCell As Range
' Setting a sample range for my testing...
Set rngFirstCell = [B12]
Set rngLastCell = [C131084]
Dim dat As Variant
Dim rng As Range
Dim i As Long
Set rng = Range(rngFirstCell, rngLastCell) ' this includes the header row
dat = rng.Columns(2)
rng.Columns(2).NumberFormat = "d/m/yyyy"
Dim intIndex As Long, varCellRef As Variant
For i = 2 To UBound(dat, 1)
dat(i, 1) = CDate(dat(i, 1))
Next
rng.Columns(2) = dat
rng.Sort Key1:=rng.Cells(1, 2), Order1:=xlAscending, _
DataOption1:=xlSortTextAsNumbers, Header:=xlYes
This ran on a sample data set in < 1sec (approx 130,000 rows)
Notice, I made a few minor tweeks to get it to run
Upvotes: 2