VBA Code: Find Last Row, Then Sort Data Based On Two Criteria

[lockedout]

I have the following code:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell.Range _
("A1:A298"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 4).Range("A1:A298"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:E299")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Which runs fine in this particular worksheet but I have data that will be ending at different row numbers.

I added:

Dim LastRow As Integer 'This is the last non empty row

LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count


Then used "LastRow" in the Range fields. Now I am getting a Run error of 1004: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort by Box isn't the same or blank.

What am I doing wrong?

Thanks

 

[Trevor G]

can you set the otpion to use CurrentRegion

Range("A2").currentRegion.Select

This would select all cells that have data in a table area

 

[lockedout]

I have never used that. Where do you place that? What code do I have to remove/alter?

Thanks,

 

[VoG]

Maybe

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

 

[Trevor G]

Look at this sample. It is sorting on Column C and A

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SortSample()<br>   Range("A1").CurrentRegion.Sort _<br>      key1:=Range("C1"), order1:=xlAscending, _<br>      key2:=Range("A1"), order2:=xlAscending, _<br>      Header:=xlYes<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[lockedout]

Thanks so much. That worked!!!!

 

[Giga_Me]

Trevor - your code fits the bill but I'm still new to VBA and I'm trying to tweak your code so that I can avoid sorting a Totals row along with the CurrentRegion data set.

Please help me to write code that will look something like this:

Sub SortSample()

   FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
   
   Myrange = Cells.CurrentRegion - (FinalRow - 1)
   
   Myrange.Sort _
      key1:=Range("A1"), order1:=xlAscending, _
      key2:=Range("F1"), order2:=xlDescending, _
      Header:=xlYes
End Sub

Thanks in advance,

Giga_Me

 

[Giga_Me]

Trevor - your code fits the bill but I'm still new to VBA and I'm trying to tweak your code so that I can avoid sorting a Totals row along with the CurrentRegion data set.

I figured out one solution myself...I'm posting with the hope this helps others:

Sub SortWithoutTotals()

FinalRow = Cells((Rows.Count), 1).End(xlUp).Row

Set Myrng = Rows(1 & ":" & FinalRow - 1)

Myrng.Sort _
      key1:=Range("F1"), order1:=xlDescending, _
      Header:=xlYes
   
End Sub