Macro to Sort Data

[howard]

I have writen code to sort data in Descending order on sheets "Overaged Inventory"

However when running the macro, I get a message, "The sort reference is not valuid.... "

The data to be sorted in in Col I


It would be appreciated if someone could kindly amend my code

 Sub Sort_Ageing_Overaged()
 Dim Lr As Long
 With Sheets("Overaged inventory")
Lr = Cells(.Rows.Count, "A").End(xlUp).Row
.Range("a2:K" & Lr).Sort Key1:=Range("I1" & Lr), Order1:=xlDescending
  End With
End Sub

 

[bebo021999]

Maybe, replace
Key1:=Range("I1" & Lr)
with:
Key1:=Range("I1")

 

[howard]

Thanks for the help. I made the change. When I run the macro from the sheet "overaged Inventory". the macro runs perfectly.

When I run from another sheet, I get same run time error


The problem was that I did not have a . before range("{i1"),order1

It shoudl be

 .Range("a2:K" & Lr).Sort Key1:=.Range("I1"), Order1:=xlDescending

 

[Peter_SSs]

The problem was that I did not have a . before range("{i1"),order1

That fixes one problem but you could easily have another. It is the same issue except this will not throw an error but may not sort your data correctly. This line of code is also missing a "."

Lr = Cells(.Rows.Count, "A").End(xlUp).Row

It should be

Lr = .Cells(.Rows.Count, "A").End(xlUp).Row

The first line above will calculate the last row based on column A of the active sheet, not "Overaged inventory"
So if the active sheet had say 5 less rows in column A compared to "Overaged inventory" then the last 5 rows in "Overaged inventory" will not get sorted.

BTW, you will find code easier to understand, debug etc if you align blocks of the code better. I have mentioned this to you several times before. One example: Macro to remove leading spaces in Col A

Sub Sort_Ageing_Overaged()
  Dim Lr As Long
  With Sheets("Overaged inventory")
    Lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("a2:K" & Lr).Sort Key1:=.Range("I1"), Order1:=xlDescending
  End With
End Sub

 

[howard]

Thanks for the advise Peter

 

[Peter_SSs]

You're welcome.