range method run time error 1004

[Kuldeep662]

Hi guys, i have small query regarding this and I havent found answer for this yet. Ofcourse I 'm new to this and not sure what do I do

I have following code which works

Range("F2 : F179").Select
Selection.Sort Key1:=Range("F2:F179"), Order1:=xlAscending _
, Order2:=xlYes, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom


Since I want to handle no. of rows dynamically for sorting i wrote following code...

Dim LastRow As Integer 'This is the LAST Non Empty Row
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Range("F2 : F & LastRow &").Select
Selection.Sort Key1:=Range("F2:F179"), Order1:=xlAscending _
, Order2:=xlYes, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Replacing 179 with LastRow variable doesnt work. When i print value of LastRow it prints correctly.

I get Method Range of Object _WorkSheet failed error. .

When i change above code to..

Dim LastRow As Integer 'This is the LAST Non Empty Row
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Worksheets("OverallITrac").Range("F2 : F & LastRow & ").Select
Selection.Sort Key1:=Range("F2:F & LastRow &"), Order1:=xlAscending _
, Order2:=xlYes, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Now I get Application defined or object defined error. PLease help. Thanks in advance.

Thanks,
Kuldeep

 

[Joe4]

Welcome to the Board!

The issue is that you have your variable LastRow in between your text qualifiers (double quotes). When you do that, it is treated as literal text and not a variable.

I have updated your code (and used a little different way to find the last row with data in column F). This code should work for you:

    Dim LastRow As Integer 'This is the LAST Non Empty Row in column F
    
    LastRow = Cells(Rows.Count, "F").End(xlUp).Row
    Worksheets("OverallITrac").Range("F2:F" & LastRow).Select
    Selection.Sort Key1:=Range("F2:F" & LastRow), Order1:=xlAscending _
        , Order2:=xlYes, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

 

[Kuldeep662]

Thanks a lot. It worked!

Cheers l_l3,
Kuldeep