Range.Sort Sort Method of Range Class Failed

[St Jimmy]

I think I'm doing something dumb since the solutions I've researched online don't work but I'm trying to sort a dynamic range of cells by their first column.

Dim LastRow As Integer
Dim LIB As String
LIB = "Library"


Sheets(LIB).Range("A65536").End(xlUp).Select
LastRow = ActiveCell.Row  'Determine last row


Sheets(LIB).Range("A9:R" & LastRow).Sort key1:=Range("A9:A" & LastRow), _
    order1:=xlAcending, Header:=xlNo

I have 4484 rows of data, column A is a text index. AB000100 - AB0014493, no cell in the col A range is empty. There are no duplicates. In the other cells the empty cells are represented by -
All cells are General Category.

When I run the macro it does not sort and spits a run-time error '1004' Sort method of Range class failed.

The data can be sorted manually, but what fun is that? I've never used Range.sort before so I think/hope it's something easy I'm forgetting...

Thank you!!!

 

[Fluff]

You're missing an order 1, it should be

order1:=xlAscending

 

[Joe4]

Try to avoid using Select/Activate statements whenever possible. They slow down your code, and are usually not necessary, i.e.

Dim LastRow As [COLOR=#ff0000]Long[/COLOR]
Dim LIB As String
LIB = "Library"

[COLOR=#ff0000]LastRow = Sheets(LIB).Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]

Sheets(LIB).Range("A9:R" & LastRow).Sort key1:=Range("A9:A" & LastRow), _
    order1:=[COLOR=#ff0000]xlAscending[/COLOR], Header:=xlNo

 

[Scott T]

In addition you can get the last row with one line of code instead of using a select

LastRow = Sheets(LIB).Cells(Rows.Count, 1).End(xlUp).Row

 

[St Jimmy]

Lol thank you. I'm glad it was easy. Stupid fat fingers and lazy eyes

 

[St Jimmy]

Thank you, thats a great point.
What benefit does declaring Integers as Longs have? Dont Ints take up less memory than long (not that it matters on modern computers...)

 

[Joe4]

The difference is that newer versions of Excel (since 2003) have over a million rows. Integers only go as high as 32,767.
So if you ever had a last row number past 32,767, it would result in errors if you declared it as Integer instead of Long.
Using Long ensures that won't be an issue.

 

[St Jimmy]

The difference is that newer versions of Excel (since 2003) have over a million rows. Integers only go as high as 32,767.
So if you ever had a last row number past 32,767, it would result in errors if you declared it as Integer instead of Long.
Using Long ensures that won't be an issue.

Thank you Joe

 

[Fluff]

In addition to what Joe4 said, I believe that integer variables are converted to long at runtime, so you might as well declare them as such to begin with.

 

[Joe4]

I believe that integer variables are converted to long at runtime, so you might as well declare them as such to begin with.

If that is true, wouldn't it NOT result in errors if your last row was greater than 32,767?
However, I did some testing and confirmed the following:

If last row in column A is 555,555
this will return an overflow error:

Dim LastRow As Integer
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

but this does not

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

If it converts Integer to Long at run-time, shouldn't the first one work without an overflow error also?