Excel VBA sort by date (without textbox)
Odeatho
-
lermite222 Posted messages 9042 Status Contributor -
lermite222 Posted messages 9042 Status Contributor -
Bonjour à tous,
I've gone through quite a few forums that provide answers that almost suit me but that I can't adapt.
I have an Excel spreadsheet with a column filled with dates and I just want to sort them by date.
I've read that the solution probably involves converting the column to standard mode.
I found a code that almost works:
Dim ColDates As Integer
Dim f1 As Worksheet
Set f1 = Worksheets("sheet")
ColDates = 40
'1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' ', _DataOption1:=xlSortNormal
'1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
However, I have two problems that I can't seem to resolve.
The first is that for the content of my columns, the first cell is a string, so when it sorts, it automatically moves it to the end, which I don't want. I've tried to change it to number during the sorting, but I couldn't succeed.
The second issue is that I understood that Excel has problems between dates dd/mm/yyyy and mm/dd/yyyy, so when I convert the column to standard format, there are certain cells that don't change (which I can't format as Date).
Do you have any ideas?
I've gone through quite a few forums that provide answers that almost suit me but that I can't adapt.
I have an Excel spreadsheet with a column filled with dates and I just want to sort them by date.
I've read that the solution probably involves converting the column to standard mode.
I found a code that almost works:
Dim ColDates As Integer
Dim f1 As Worksheet
Set f1 = Worksheets("sheet")
ColDates = 40
'1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' ', _DataOption1:=xlSortNormal
'1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
However, I have two problems that I can't seem to resolve.
The first is that for the content of my columns, the first cell is a string, so when it sorts, it automatically moves it to the end, which I don't want. I've tried to change it to number during the sorting, but I couldn't succeed.
The second issue is that I understood that Excel has problems between dates dd/mm/yyyy and mm/dd/yyyy, so when I convert the column to standard format, there are certain cells that don't change (which I can't format as Date).
Do you have any ideas?
11 answers
Hello,
Indeed, it is not possible to change a date entered as text into date format.
The reason is that Excel treats dates as numbers '1 = 1/1/1900
the 13/11/2008 = 39765
This means that in the cell there isn't a date but a number that Excel DISPLAYS in the specified format.
However, in a text cell, it is not a number but a string.
And there is no issue with the formats.
To remedy this, you can loop through your column to convert everything back to date with..
Of course, adjust the addresses accordingly.
Once that's done, your filter will work normally.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that's empty. ;-)(Confucius)
Indeed, it is not possible to change a date entered as text into date format.
The reason is that Excel treats dates as numbers '1 = 1/1/1900
the 13/11/2008 = 39765
This means that in the cell there isn't a date but a number that Excel DISPLAYS in the specified format.
However, in a text cell, it is not a number but a string.
And there is no issue with the formats.
To remedy this, you can loop through your column to convert everything back to date with..
Worksheets("Feuil2").Range("A7") = CVDate(Worksheets("Feuil2").Range("A7")) Of course, adjust the addresses accordingly.
Once that's done, your filter will work normally.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that's empty. ;-)(Confucius)
Hello,
while waiting for our friend Louis (hello, Lhermite it's time!) ;-)
your variable A being a number designating a column, you can hardly use the object "range"
so you need to use cells(12,A)
if your variable X designated a row in column B, you could write
range("B:" & X)
--
Best regards,
Michel
while waiting for our friend Louis (hello, Lhermite it's time!) ;-)
your variable A being a number designating a column, you can hardly use the object "range"
so you need to use cells(12,A)
if your variable X designated a row in column B, you could write
range("B:" & X)
--
Best regards,
Michel
Thank you, Michel =)
I tried to do what you told me, but it didn't work. I have the impression that I just don't understand it, lol.
What I actually want is to go through my column L or AN, whichever, from 1 to 250.
I tested this (in a loop from 1 to 250 with A as the incrementing variable):
f1.Range("AN:" & A) = CVDate(f1.Range("AN:" & A))
it tells me "method range of object worksheet failed"
And this:
f1.Cells("AN:" & A) = CVDate(f1.Cells("AN:" & A))
it tells me "Type mismatch"
I don't understand what's wrong.
I tried to do what you told me, but it didn't work. I have the impression that I just don't understand it, lol.
What I actually want is to go through my column L or AN, whichever, from 1 to 250.
I tested this (in a loop from 1 to 250 with A as the incrementing variable):
f1.Range("AN:" & A) = CVDate(f1.Range("AN:" & A))
it tells me "method range of object worksheet failed"
And this:
f1.Cells("AN:" & A) = CVDate(f1.Cells("AN:" & A))
it tells me "Type mismatch"
I don't understand what's wrong.
Hello, hello Michel, here I am, I'm awake... :-)
No, it's really CVDate...
And let's start from the beginning, change your Textre column to date...
1°) with Range you first put the column AND THEN the row... Like B5
But with Cells it's the opposite, Cells(Row, Column)
So when you put...
Cells(40, A).Value = Cells(12, A).Value
The addresses are not correct.
First, run this little function
You say..
And we'll see for the rest
EDIT:
Avoid using abstract variables like A, B, C etc., if you’re dealing with rows use a variable that makes you think of rows, I use Lig and Col for columns.
Also, try to include one or more capital letters in the variable name but when you transcribe it, DO NOT USE capital letters, so you can immediately detect a syntax error if the word you just typed doesn't transform by itself with one or more capital letters. (little tricks)
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-) (Confucius)
No, it's really CVDate...
And let's start from the beginning, change your Textre column to date...
1°) with Range you first put the column AND THEN the row... Like B5
But with Cells it's the opposite, Cells(Row, Column)
So when you put...
Cells(40, A).Value = Cells(12, A).Value
The addresses are not correct.
First, run this little function
Sub ChangerT_D() Dim Lig As Long, Col As Integer Col = 2 ' For column B .. to adapt. Note: for column AN it’s 40 For Lig = 2 To 250 'adapt the rows Cells(Lig, Col) = CVDate(Cells(Lig, Col)) Next Lig End Sub
You say..
And we'll see for the rest
EDIT:
Avoid using abstract variables like A, B, C etc., if you’re dealing with rows use a variable that makes you think of rows, I use Lig and Col for columns.
Also, try to include one or more capital letters in the variable name but when you transcribe it, DO NOT USE capital letters, so you can immediately detect a syntax error if the word you just typed doesn't transform by itself with one or more capital letters. (little tricks)
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-) (Confucius)
I will still answer your second question before your response,
Key1:=Range("I250"), I doubt this is correct,
Key is the "word" to search for, so if for example what you are looking for is in a list of names you type in A1 ...Marcel
and it should then be Key=range("A1"), it will search all the lines where the name Marcel is written.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
Key1:=Range("I250"), I doubt this is correct,
Key is the "word" to search for, so if for example what you are looking for is in a list of names you type in A1 ...Marcel
and it should then be Key=range("A1"), it will search all the lines where the name Marcel is written.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
Hello everyone,
Thank you, I will test all this (by Wednesday evening next week and yes, long live alternation :p)
I'll keep you posted.
So actually, the function I wrote in my first post doesn't sort by date at all then!
Or maybe it's designed for there to be nothing in the box in question, in which case the sort would be in ascending order?
Anyway, these are just assumptions. Strange though, I took this function from a forum that provided functions for sorting by date.
Thank you very much!
Thank you, I will test all this (by Wednesday evening next week and yes, long live alternation :p)
I'll keep you posted.
So actually, the function I wrote in my first post doesn't sort by date at all then!
Or maybe it's designed for there to be nothing in the box in question, in which case the sort would be in ascending order?
Anyway, these are just assumptions. Strange though, I took this function from a forum that provided functions for sorting by date.
Thank you very much!
Re,
Probably because there is a mix of formats,
try setting the whole column to Text.. if that doesn't work, try Standard..
You say.
A+
EDIT:
Negative, changing the format didn't work.
--
Experience teaches more surely than advice. (André Gide)
If you hit a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-)(Confucius)
Probably because there is a mix of formats,
try setting the whole column to Text.. if that doesn't work, try Standard..
You say.
A+
EDIT:
Negative, changing the format didn't work.
--
Experience teaches more surely than advice. (André Gide)
If you hit a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-)(Confucius)
Try rather with...
You say.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-) (Confucius)
Sub ChangeT_D() Dim Row As Long, Col As Integer Col = 2 ' For column B .. to be adjusted. Note: for column AN it's 40 On Error resume Next For Row = 2 To 250 'adjust the rows Cells(Row, Col) = CVDate(Cells(Row, Col)) Next Row End Sub
You say.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-) (Confucius)
Thank you Lermitte22,
I feel that it’s progressing a lot, I thank you (I haven’t forgotten the others, you know :) )
So let’s say it doesn’t sort them in ascending order (but I don’t think that’s due to the formatting)
I will give you the exact code of the macro
and also in the cases where there’s nothing (I put 250 to make sure I don’t miss any, but I think there are only about 150 filled cells), there is now 00/01/1900 but I think it’s because it considers that the empty cell represents 0.
I just want to understand now why it doesn’t sort in the correct order.
Sub SortByDate()
Dim f1 As Worksheet
Set f1 = Worksheets("Actions GO")
Dim Lig As Long, Col As Integer, ColDep As Integer
Col = 40 ' For column B .. to adapt. Note: for column AN it’s 40
ColDep = 12
On Error Resume Next
For Lig = 2 To 250 'adapt the rows
Cells(Lig, Col).Value = Cells(Lig, ColDep).Value
Cells(Lig, Col) = CVDate(Cells(Lig, Col))
Next Lig
Cells(1, Col) = 1
Dim A As Long
For A = 2 To 250
Next A
Cells(40, 1).Value = "1000"
Dim ColDates As Integer
ColDates = 40
f1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
', _DataOption1:=xlSortNormal
f1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
End Sub
It is probably poorly written or not written like a real coder, I apologize in advance.
I thank you all
I feel that it’s progressing a lot, I thank you (I haven’t forgotten the others, you know :) )
So let’s say it doesn’t sort them in ascending order (but I don’t think that’s due to the formatting)
I will give you the exact code of the macro
and also in the cases where there’s nothing (I put 250 to make sure I don’t miss any, but I think there are only about 150 filled cells), there is now 00/01/1900 but I think it’s because it considers that the empty cell represents 0.
I just want to understand now why it doesn’t sort in the correct order.
Sub SortByDate()
Dim f1 As Worksheet
Set f1 = Worksheets("Actions GO")
Dim Lig As Long, Col As Integer, ColDep As Integer
Col = 40 ' For column B .. to adapt. Note: for column AN it’s 40
ColDep = 12
On Error Resume Next
For Lig = 2 To 250 'adapt the rows
Cells(Lig, Col).Value = Cells(Lig, ColDep).Value
Cells(Lig, Col) = CVDate(Cells(Lig, Col))
Next Lig
Cells(1, Col) = 1
Dim A As Long
For A = 2 To 250
Next A
Cells(40, 1).Value = "1000"
Dim ColDates As Integer
ColDates = 40
f1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
', _DataOption1:=xlSortNormal
f1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
End Sub
It is probably poorly written or not written like a real coder, I apologize in advance.
I thank you all
Hello,
S, I think it comes from the formatting,
First, set it to standard or number format (I can't remember), which, since Excel treats dates as numbers, translates your dates into numbers, and the sorting is then done based on these numbers.
Then you set the format back to date
You don't see it because it happens too quickly.
But it's a good tip to remember for sorting by date.
See you later
EDIT:
What do you mean by "doesn't sort in the right order"? If you want to change the order, just change xlAscending to xlDescending
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;)(Confucius)
S, I think it comes from the formatting,
First, set it to standard or number format (I can't remember), which, since Excel treats dates as numbers, translates your dates into numbers, and the sorting is then done based on these numbers.
Then you set the format back to date
You don't see it because it happens too quickly.
But it's a good tip to remember for sorting by date.
See you later
EDIT:
What do you mean by "doesn't sort in the right order"? If you want to change the order, just change xlAscending to xlDescending
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;)(Confucius)
Yes Lermitte22, that was the principle behind it: copying the date column, transforming it into date format, then sorting it from smallest to largest, and finally reformatting it back to date.
When I say it’s in the wrong order, I mean like this:
15/11/2008
15/10/2008
15/05/2009
31/10/2008
10/08/2008
31/10/2008
31/10/2008
26/10/2007
15/10/2008
15/10/2008
So I don’t quite get what’s wrong, isn’t there something simpler than what I wrote above just to sort the numbers? I don’t need to reformat it to date at the end since I'm copying it into an unused column.
When I say it’s in the wrong order, I mean like this:
15/11/2008
15/10/2008
15/05/2009
31/10/2008
10/08/2008
31/10/2008
31/10/2008
26/10/2007
15/10/2008
15/10/2008
So I don’t quite get what’s wrong, isn’t there something simpler than what I wrote above just to sort the numbers? I don’t need to reformat it to date at the end since I'm copying it into an unused column.
However, with the idea of changing the format, it works perfectly...
Result...
I tested with the example you provided..
See you+
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-) (Confucius)
Sub SortByDate() Dim Range As Range Dim Col As Byte, StartRow As Long, LastRow As Long Sheets("SortByDate").Select Col = 6 'for column F StartRow = 5 '1st row of sorting LastRow = 14 Set Range = Range(Cells(StartRow, Col), Cells(LastRow, Col)) Range.Select Selection.NumberFormat = "0" Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.NumberFormat = "dd/mm/yyyy" Range("F17").Select End Sub Result...
26/10/2007 10/08/2008 15/10/2008 15/10/2008 15/10/2008 31/10/2008 31/10/2008 31/10/2008 15/11/2008 15/05/2009
I tested with the example you provided..
See you+
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-) (Confucius)
A bit more automatic...
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
Sub TrieDate() Dim Plage As Range Dim Col As Byte, LigDeb As Long, DerLig As Long Sheets("TriDate").Select Col = 6 'for column F LigDeb = 5 '1st row of sort DerLig = Cells(65536, Col).End(xlUp).Row Set Plage = Range(Cells(LigDeb, Col), Cells(DerLig, Col)) Plage.NumberFormat = "0" Plage.Sort Key1:=Cells(LigDeb, Col), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Plage.NumberFormat = "dd/mm/yyyy" End Sub --
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
Thank you, everyone, it's progressing well, there's almost nothing left.
I still have a problem, actually, I took your macro lermitte22 and when I run it, my dates are sorted first by day and then by month, I get this in fact:
01/01/2008
01/03/2009
01/07/2008
01/08/2008
01/08/2008
01/09/2008
01/11/2008
02/10/2008
02/10/2008
03/09/2008
03/10/2008
03/10/2008
03/11/2008
(the list is long but I'm giving you a simple example)
So I thought I'll remove the last line to see how the formatting goes and in fact only cell 3 goes to standard mode (my first row is row 2)
I think the whole problem comes from the fact that my cell 1 in the date column is text, that must cause an issue for formatting the entire column.
That's why at first I tried to copy my column into the empty column AN and I put 1 as a standard value in cell 1 so that row 1 wouldn't move.
It's complicated :s
I still have a problem, actually, I took your macro lermitte22 and when I run it, my dates are sorted first by day and then by month, I get this in fact:
01/01/2008
01/03/2009
01/07/2008
01/08/2008
01/08/2008
01/09/2008
01/11/2008
02/10/2008
02/10/2008
03/09/2008
03/10/2008
03/10/2008
03/11/2008
(the list is long but I'm giving you a simple example)
So I thought I'll remove the last line to see how the formatting goes and in fact only cell 3 goes to standard mode (my first row is row 2)
I think the whole problem comes from the fact that my cell 1 in the date column is text, that must cause an issue for formatting the entire column.
That's why at first I tried to copy my column into the empty column AN and I put 1 as a standard value in cell 1 so that row 1 wouldn't move.
It's complicated :s
Re,
Even in text it should change unless there are spaces in the cells.
To test, put in an empty column =LEN(..) and check if it shows the number of characters you see.
If more, let me know, I'll send you a macro that corrects this.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-) (Confucius)
Even in text it should change unless there are spaces in the cells.
To test, put in an empty column =LEN(..) and check if it shows the number of characters you see.
If more, let me know, I'll send you a macro that corrects this.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-) (Confucius)
However, I don't have a great level in VBA and I'm having trouble managing Ranges.
You see in the piece of code I quoted in my first post, I don't understand why it's Range(A1) then Key(I250).
And I tried to use your formula but it's the same, I can't tell it to take from cell 1 to 250.
Another question, how do we include column AN in a range? I haven't managed to do that either.
Here is the code I put, but the range is not correct.
Dim A As Long
For A = 2 To 250
Cells(40, A).Value = Cells(12, A).Value
f1.Range(12, A) = CVDate(f1.Range(12, A))
Next A
I'm probably asking easy questions, sorry :)