CODEWITHSUNDEEP
exceloptimizationsortingvbaexcel-2003
tobriand
tobriand

Reputation: 1167

How to sort a column of mixed date and general format data using VBA in Excel 2003

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

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

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

Related Questions