Compile Error: Expected List Separator or ) when trying LastRow Sort

Question

Hi,

I'm a novice to VBA.  I'm using Excel 2003.  Can anyone tell me why the following code keeps giving me the compile error for Expected List Separator or ).  I Google a lot of code to help me and this was copied from a post that the user said worked great.  All I changed was the Sheet # and the Range.  What I"m trying to accomplish is to sort the data on my worksheet by Column T and only including rows that have actually data in them. Can anyone help.  I'm totally stumped.

Thanks!

Dim LastRow As Integer 'This is the LAST Non Empty Row

    LastRow = ActiveSheet.UsedRange.Row - 1 + _

            ActiveSheet.UsedRange.Rows.Count

    Worksheets("Sheet1").Range("A1:T" & LastRow & ").Sort Key1:=Worksheets("Sheet1").Columns("T"), _

        Order1:=xlAscending, Header:=xlYes, _

        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

        DataOption1:=xlSortNormal

Answer 1

Well, the compile error message is gone but I still have my original problem where it sorts 3000 rows when I actually only have 1168 rows of data and puts the blanks at the top.  The range that I want to sort has to be dynamic as the rows of data will only increase as this spreadsheet continues to get used.  I was hoping to avoid the blank rows at the top after the sort by using LastRow.  Is there an easier way to learn this stuff?

I think the problem is in the way you are calculating the last row. The UsedRange is property does not always return what you think it should. If there was data in a row below your final data that was deleted, Excel's UsedRange property can, under certain circumstances, keep remembering that cell as being the last data cell even though nothing is in it anymore. Also, if you format cells below your actual data, Excel's UsedRange will consider them as being used even if you don't put data in them yet. Try using this method of calculating the last row instead...

Dim LastRow As Long  '<< Note I Dim'med this as Long, not Integer

LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Note the above will find the last displayed data value and it will ignore formulas displaying the empty string (""). If you need to locate the last row with either a value or formula (no matter what the formula is displaying, including the empty string) then use this instead...

Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 2

Besides what HansV mentioned, Key1 has to refer to a single cell, not an entire column.

You should dim LastRow as Long rather than as Integer, because the integer variable can only contain values up to -/+32767, which may be to little if you have a larger ammoun of data. The Long variable type can hold values up to +/- 2,147,483,647.

Dim LastRow As Long 'This is the LAST Non Empty Row

LastRow = ActiveSheet.UsedRange.Row - 1 + _

            ActiveSheet.UsedRange.Rows.Count

Worksheets("Sheet1").Range("A1:T" & LastRow).Sort _

    Key1:=Worksheets("Sheet1").Range("T1"), Order1:=xlAscending, _

    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _

    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Answer 3

Worksheets("Sheet1").Range("A1:T" & LastRow & ") should be  Worksheets("Sheet1").Range("A1:T" & LastRow).

Answer 4

While I don't know exactly what LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row all means it seems to have worked.

Thanks everyone

Answer 5

Well, the compile error message is gone but I still have my original problem where it sorts 3000 rows when I actually only have 1168 rows of data and puts the blanks at the top.  The range that I want to sort has to be dynamic as the rows of data will only increase as this spreadsheet continues to get used.  I was hoping to avoid the blank rows at the top after the sort by using LastRow.  Is there an easier way to learn this stuff?

Thanks for your help.