vba code sort multiple columns getting compile error!

[RAM1972]

Hi All

Tried a macro to sort column H,I,J to last data row, rows 1 are headers

the range are columns A to S to last data row

but getting a compile error

expected end with

Can anyone help

Sub testsort()


Dim LR As Long
With ActiveWorkbook.Worksheets("datatest")
    LR = .Range("A" & Rows.Count).End(xlUp).Row.Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("H1:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("I1:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("J1:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange .Range("A1:S" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub:confused:

 

[MARK858]

You need another End With on a line after the End With you already have.

 

[RAM1972]

You need another
End With on a line after the End With you already have.

Add line end with as requested but get a yellow line

Run time error 424
object required

LR = .Range("A" & Rows.Count).End(xlUp).Row.Sort.SortFields.Clear

Any idea what is going wrong

 

[Sektor]

Row is Long type. It doesn't have Sort property.
Anyway, what you're trying to achieve with this line?

 

[MARK858]

Probably just clearing any previous sort fields and so should be on a separate line (must admit I can't remember a time when I needed to but the recorder always throws it in).

Sub testsort()


Dim LR As Long
With ActiveWorkbook.Worksheets("datatest")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("H1:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("I1:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("J1:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange .Range("A1:S" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

End Sub

 

[RAM1972]

Row is Long type. It doesn't have Sort property.
Anyway, what you're trying to achieve with this line?

As per code above.

I have data from A to S
Row 1 are headers
Data starts at rows 2
Sheet name is datatest
from Active workbook whole range from A to S but sort criteria column H , I and J.

Hope it helps