VBA Runtime 438 error caused by 2003 version

[molsonlabatt]

Hi,

I have completed my code with 2010 and have have checked it on 2007 and both work great. When I try and use it on excel 2003 I get the runtime 438 error. The error occurs (in bold) on the Sort portion of my code, shown below.

Is there anyway I can modify the code to work with excel 2003

Thanks

' Gets last row number and last column number
LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

' Create name for all sort area
 Set SortA = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))

' Sort cells by last column
Range("A1").Select
 [B]ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear[/B]
 ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Worksheets("Sheet1").Cells(1, LastCol), Worksheets("Sheet1").Cells(LastRow, LastCol)), _
 SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Sheet1").Sort
   .SetRange SortA
   .Header = xlGuess
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
 End With

 

[RoryA]

There is no Sort object in 2003, so you have to use the old Sort method instead - untested:

' Gets last row number and last column number
LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

' Create name for all sort area
 Set SortA = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))

' Sort cells by last column
SortA.Sort Key1:=SortA.Cells(1), Order:=xlAscending, Header:=xlGuess

 

[hiker95]

molsonlabatt,

I am currently using Excel 2007.

If I understand you correctly "Sort cells by last column".

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	1	1	1	1	1	1	1	1	1	1	10
2	2	2	2	2	2	2	2	2	2	2	9
3	3	3	3	3	3	3	3	3	3	3	8
4	4	4	4	4	4	4	4	4	4	4	7
5	5	5	5	5	5	5	5	5	5	5	6
6	6	6	6	6	6	6	6	6	6	6	5
7	7	7	7	7	7	7	7	7	7	7	4
8	8	8	8	8	8	8	8	8	8	8	3
9	9	9	9	9	9	9	9	9	9	9	2
10	10	10	10	10	10	10	10	10	10	10	1
11
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	10	10	10	10	10	10	10	10	10	10	1
2	9	9	9	9	9	9	9	9	9	9	2
3	8	8	8	8	8	8	8	8	8	8	3
4	7	7	7	7	7	7	7	7	7	7	4
5	6	6	6	6	6	6	6	6	6	6	5
6	5	5	5	5	5	5	5	5	5	5	6
7	4	4	4	4	4	4	4	4	4	4	7
8	3	3	3	3	3	3	3	3	3	3	8
9	2	2	2	2	2	2	2	2	2	2	9
10	1	1	1	1	1	1	1	1	1	1	10
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub SortCellsByLastColumn()
' hiker95, 01/20/2014, ME751804
Dim SortA As Range
Dim LastRow As Long, LastCol As Long
Dim LastColName As String, h As String

' Sort cells by last column

With Sheets("Sheet1")
  LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
  LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
  Set SortA = .Range(Cells(1, 1), .Cells(LastRow, LastCol))
  LastColName = Replace(Cells(1, LastCol).Address(0, 0), 1, "")
  h = LastColName & 1
  SortA.Sort key1:=.Range(h), order1:=1
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SortCellsByLastColumn macro.

 

[hiker95]

molsonlabatt,

I have used similar code in Excel 2007, and, Excel 2003.

You will have to test in all three versions of Excel that you have.

 

[molsonlabatt]

molsonlabatt,

I am currently using Excel 2007.

If I understand you correctly "Sort cells by last column".

Sample raw data:

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	1	1	1	1	1	1	1	1	1	1	10
2	2	2	2	2	2	2	2	2	2	2	9
3	3	3	3	3	3	3	3	3	3	3	8
4	4	4	4	4	4	4	4	4	4	4	7
5	5	5	5	5	5	5	5	5	5	5	6
6	6	6	6	6	6	6	6	6	6	6	5
7	7	7	7	7	7	7	7	7	7	7	4
8	8	8	8	8	8	8	8	8	8	8	3
9	9	9	9	9	9	9	9	9	9	9	2
10	10	10	10	10	10	10	10	10	10	10	1
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro:

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	10	10	10	10	10	10	10	10	10	10	1
2	9	9	9	9	9	9	9	9	9	9	2
3	8	8	8	8	8	8	8	8	8	8	3
4	7	7	7	7	7	7	7	7	7	7	4
5	6	6	6	6	6	6	6	6	6	6	5
6	5	5	5	5	5	5	5	5	5	5	6
7	4	4	4	4	4	4	4	4	4	4	7
8	3	3	3	3	3	3	3	3	3	3	8
9	2	2	2	2	2	2	2	2	2	2	9
10	1	1	1	1	1	1	1	1	1	1	10
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub SortCellsByLastColumn()
' hiker95, 01/20/2014, ME751804
Dim SortA As Range
Dim LastRow As Long, LastCol As Long
Dim LastColName As String, h As String

' Sort cells by last column

With Sheets("Sheet1")
  LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
  LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
  Set SortA = .Range(Cells(1, 1), .Cells(LastRow, LastCol))
  LastColName = Replace(Cells(1, LastCol).Address(0, 0), 1, "")
  h = LastColName & 1
  SortA.Sort key1:=.Range(h), order1:=1
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SortCellsByLastColumn macro.

Thanks hiker95 it works just as I want.
cheers

 

[hiker95]

molsonlabatt,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.