Sorting In Excel

[SandraF]

I am trying to sort some rows in a worksheet By Column I and then by Column L. There could be any number of rows when the person runs the macro. So that is never constant. I have the following code so far and I keep getting the error - "object doesn't support method."

- The A6 is the line that the data will always start on

Option Explicit
Option Base 1
Sub SortDb()

Worksheets("DB&quot.Range("A6&quot.Sort _
Key1:=Worksheets("DB&quot.Column("I&quot, _
Key2:=Worksheets("DB&quot.Column("L&quot

End Sub

What am I doing wrong?

 

[DarkSun]

The best thing to do is record a macro of doing the sort then look at the code it produces.

It will give you something like this:

Range("A1:M17&quot.Select
Selection.Sort Key1:=Range("I1&quot, Order1:=xlAscending, Key2:=Range("L1&quot _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

 

[JVFriederick]

A trick to making sorts work is to have continuous "vertical" data starting in the selected row (6 in this case) and continuous "horizontal" data starting in the selected column (A in this case). Otherwise, use a named range and select this instead of A6.

Range("A6&quot.Select ' Select range, sort by the SortSequence
Selection.Sort _
Key1:=Range("I6&quot, Order1:=xlAscending, _
Key2:=Range("L6&quot, Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom

 

[kruskovac]

I do this in two steps--one to find the number of rows, dynamically and the next to actually do the sorting on the range selected.

Here's the subroutine to find the number of rows on a worksheet:

Sub GetRows()
Dim C As Range
Worksheets(1).Activate
[F10:F10010].Select
For Each C In Selection.Cells
If C.Value <> &quot;&quot; Then
Counter = Counter + 1
End If
Next C
End Sub
What happens is that it selects a range that is much, much bigger than I need (F10:F10010). It then loops through to find the first instance of a null value in a cell.

Here's the next part:
If optLoanDate.Value = True And optAscending = True Then
Range(Cells(9, 1), Cells(Counter + 9, 9)).Select
Selection.SORT Key1:=Range(&quot;A10&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(&quot;A9&quot.Select
ElseIf optLoanDate.Value = True And optDescending = True Then
Range(Cells(9, 1), Cells(Counter + 9, 9)).Select
Selection.SORT Key1:=Range(&quot;A10&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(&quot;A9&quot.Select

Here I have the user select from different radio buttons on what column heading they want to sort by and then is it asc or desc sorting. The key here is to have the range(cell(x,y),cells(x,y)) . Counter is a global value used in the two different subs. If you want a copy of the excel file, let me know.

You can also turn off the screenupdating to make it a little faster, but users love it when they can see all that stuff happening...