Sorting a table using vba code

[dpaton05]

I am trying to sort a table by the date column with this code:

    'declare variables
    Dim combo As String                                                     'Combo worksheet name
    
    'assign values to variables
    combo = Worksheets("Home").Range("Q5")                                  'string in cell Q5 of Home worksheet
    
    Range(combo).CurrentRegion.Sortkey1:=Range("Date of work"), order1:=xlAscending, Header:=xlYes

Combo has the relevant sheet name stored within it and the table goes up column Q. Can anyone tell me what I have done wrong with this code please as it gives me a syntax error and highlights the range(combo) line of code?

Thanks,
Dave

 

[Michael M]

your sort key1 is not defined anywhere in the code !!

Range("Date of work")

your need to tell the macro what date of work is OR where it resides, similar to what you did with combo !!

 

[dpaton05]

How would I do that, Date of work refers to the first column in my tables?

 

[Michael M]

OK, but I'm guessing that is the heder of that column.
A normal sort key would be something like

Sort Key1:=Range("A2")

Try srting by the first date in that column instead.
Using named ranges is great but can make it almost impossible for someone else to modify or debug.
You really would be better of using actual ranges rather than names until the code works THEN start substituting Named Range names !

 

[MARK858]

Michael, I think when the OP is referring to a "Table" they do mean a Table i.e. what we used to call a ListTable so they are selecting the range then doing insert - table.

Unfortunately I can't remember the syntax we used to use in the old pre 2007 style of coding for a sort using a ListTable.

 

[dpaton05]

Michael, I think when the OP is referring to a "Table" they do mean a Table i.e. what we used to call a ListTable so they are selecting the range then doing insert - table.

Unfortunately I can't remember the syntax we used to use in the old pre 2007 style of coding for a sort using a ListTable.

That's it, I am using a table from selecting the data then choosing insert, table or pressing ctrl t.

 

[dpaton05]

Is there a problem with using a table? I have just read that you should always use a table when dealing with data in excel.

 

[MARK858]

Is there a problem with using a table?

Not as such, it is personal preference. They have advantages (they are especially useful if you are dealing with people who don't know Excel well) so some prefer them but others don't.

I fall in the 2nd category.

The syntax for coding I find annoying which is probably why I don't use them much (I also wish people wouldn't make statements like "should always use a table" rather than let people judge for themselves what is best in their situation as it is just an opinion ).

 

[dpaton05]

Maybe I will try getting rid of the tables and use that sorting code and see how that goes. Thanks

 

[dpaton05]

I converted it back to not use tables and now I was going to use the xldown method to select the dates to sort. I have this code for that:

worksheets(combo).Range("A4").End(xlDown)

What code do I need to add to sort the range considering the range has headings in A3:D3, so the data doesn't start until row 4.

 

[MARK858]

Maybe (untested)

Worksheets(combo).Range("A4:D" & Worksheets(combo).Range("A4").End(xlDown).Row).Sort key1:=Worksheets(combo).Range("A4"), order1:=xlAscending, Header:=xlNo