VBA Help

[Miya]

Hi, the below recorded macro sorts data in ascending order in Col C, I need this code to be cleaned up so that it is flexible with the ranges as the data will not always finish at row 650

.SetRange Range("A5:J650")
:=Range("C6:C650"), _

Sub Macro2()
'
' Macro2 Macro
'
'
    Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Rec").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Rec").Sort.SortFields.Add Key:=Range("C6:C650"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Rec").Sort
        .SetRange Range("A5:J650")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[matthew230]

Will you have continous data ?

How are you planning to determine which cell will be the last one if is not 650 ?

Is their any data in the cells beyond the last cell required ?

 

[Miya]

Will you have continous data ?

How are you planning to determine which cell will be the last one if is not 650 ?

Is their any data in the cells beyond the last cell required ?

There isnt any data after the last cell, but when i do CTRL * for current region it selects the data beyond the data range, i do have formatting beyond the data range, but i thought that would not effect it. I did CTRL End and the cursor pointed Cell V1433, i deleted all rows after the range and its still points to V1433, so when i run the sort manually i get blank rows and the date is futher down.

 

[lenze]

Range("$C$6:$J" & Range("$J$65536").End(xlUp).Row)

lenze

 

[Miya]

Range("$C$6:$J" & Range("$J$65536").End(xlUp).Row)

lenze

lenze where in the code do i put your line, is there any lines that can be taken out?

Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Rec").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Rec").Sort.SortFields.Add Key:=Range("C6:C650"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Rec").Sort
        .SetRange Range("A5:J650")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[lenze]

Replace what you have

.SetRange Range("A5:J650")

becomes

.setRange Range("$C$6:$J" & Range("$J$65536").End(xlUp).Row)

same for your columns C
lenze

 

[Miya]

Replace what you have

.SetRange Range("A5:J650")

becomes

.setRange Range("$C$6:$J" & Range("$J$65536").End(xlUp).Row)

same for your columns C
lenze

Ok i did that, and i got a run time error 1004, the sort ref is not valid

.Apply

 Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Rec").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Rec").Sort.SortFields.Add Key:=Range("$C$6:$J" & Range("$J$65536").End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Rec").Sort
        .SetRange Range("$C$6:$J" & Range("$J$65536").End(xlUp).Row)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Miya]

ok i tried to trim the code down to this, but when i run the code i am still getting blank rows and the data somewhere in the middle, what have i done wrong?

With .Range("$A$5:$M" & Range("$M$65536").End(xlUp).Row)
.Sort Key1:=.Range("$D$6:$M" & Range("$M$65536").End(xlUp).Row), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With