Excel VBA using Range in Macro

Question

I need to create a macro in Excel 2007 that will sort. I don't know how many rows there will be. I know one way to to find the number of rows and how to record sorting, but not how to use these bits of code together.

Sub Sort()
'
' Sort Macro
'   *find the last row (assuming no more than 100000 rows)*
    Dim Row As Range
    Set Row = Range("A100000").End(xlUp).Select

'  *code written by recording my sort*
    Range("A1:G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B6376" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D6376" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F6376" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:G6376")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I've tried to put "Row" in multiple places, but I get the RUn-time error '424' Object Required. I need this variable to replace the row number (6376) but not sure how to do it.

I can see where these lines

Range("A1:G1").Select
Range(Selection, Selection.End(xlDown)).Select

are selecting the contents of the workbook, which is what I want, I just don't know how to do it dynamically.

EDIT: I want to sort and subtotal. This is the recorded macro. I need to change the 6376 to be dynamic according to how many rows there are.

Sub Macro4()
'
' Macro4 Macro
'

'
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B6376" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D6376" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F6376" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:G6376")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

Thanks.

[Interesting Read](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) — Siddharth Rout, Sep 11 '14 at 17:41
Thanks @SiddharthRout. That is a helpful link, but I'm still at a loss how to use a dim with a rows that are not known. For example, Set rng = Range("A1:B10") - If I don't know that the columns end at "B" and the rows end at "10", how do I plug that into the dim? — jabs, Sep 11 '14 at 18:24
Does [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) put you in the right direction :) — Siddharth Rout, Sep 11 '14 at 19:26
@jabs If I recall things correctly, this code will never run in Excel 2003. The SortFields object was added in 2007; and the maximum number of rows is 65534. — Ron Rosenfeld, Sep 11 '14 at 19:35
@RonRosenfeld: Thanks for the comment. My mistake. I am using 2007. — jabs, Sep 11 '14 at 19:42
@jabs So you want a macro that will run in 2007, and does not **need** to run in 2003? — Ron Rosenfeld, Sep 11 '14 at 19:49
@SiddharthRout: +1 again for the good post. But, I think I have a good method for finding the last row. I don't have a good idea of how to put it into the script. I'm using this to find the last row: `Dim lastRow As Long` `Set lastRow = Range("A100000").End(xlUp).Row` But haven't had success applying the lastRow variable to replace the second part of the hard-coded range. — jabs, Sep 11 '14 at 19:51
No that is not a good way because you are hardcoding `100000` what if there is data in `A100001`? Your `Set lastRow = Range("A100000").End(xlUp).Row` will not give you the correct last row and hence see the link above :) — Siddharth Rout, Sep 11 '14 at 19:58
@jabs Also, I don't see where you really need to **KNOW** what the last row is. You only use that parameter to construct a range object, and that can be obtained without having that information. — Ron Rosenfeld, Sep 11 '14 at 20:03
@SiddharthRout: I know the rows won't go above 100000 and I'm being a little lazy :-) — jabs, Sep 11 '14 at 20:07
@RonRosenfeld: I may not need to know it - but I still need to know how to plug in the constructed range object where I want it to go. — jabs, Sep 11 '14 at 20:08
@jabs Take a look at my answer for a method of doing that for Sorting; for subtotals, you just need the groupby field number, no range object except for the total range, which has already been determined by .CurrentRegion. So in my code, RG.Columns(2) is the object that consists of everything in Column B from row 1 to whatever the last row happens to be. — Ron Rosenfeld, Sep 11 '14 at 20:15

Ron Rosenfeld · Accepted Answer · 2014-09-12T13:40:13.127

Not being certain of your data setup, you can try the following, which includes a simple sort routine for columns B, D and F, assuming your data starts in column A (it will also run in 2003, but I guess that's not an issue). I did not include MatchCase below as in your code, it was a matter of the recording, and not necessarily what you want; but you can decide.

EDIT Routine for doing subtotals added

EDIT2 Header parameter added to Sort

Option Explicit
Sub SortAndSubtotal()
    Dim RG As Range
    Dim WS As Worksheet

Set WS = Worksheets("Sheet2") '<--Change as needed
Set RG = WS.Range("a1").CurrentRegion

With RG
    .Sort key1:=.Columns(2), order1:=xlAscending, _
        key2:=.Columns(4), order2:=xlAscending, _
        key3:=.Columns(6), order3:=xlAscending, _
        Header:=xlYes, MatchCase:=False
    .Sort key1:=.Columns(1), order1:=xlAscending, Header:=xlYes
End With

'Note that I am just selecting a single cell in the range, since the range will
'  expand with each Subtotal.  One could also use
'  RG.CurrentRegion as the Range Object Expression, but you need to use it
'  individually for each .Subtotal operation, to handle the expansion issue.
'  Or you could use With RG and then prefix each Subtotal line with .CurrentRegion

With RG(1)
    .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), _
        Replace:=True, SummaryBelowData:=True
    .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7), _
        Replace:=False, SummaryBelowData:=True
    .Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _
        Replace:=False, SummaryBelowData:=True
End With

End Sub

+1 Thanks - This works for sorting, but I've expanded the question to include the subtotal. — jabs, Sep 11 '14 at 21:27
@jabs Subtotal routine added. Also, I note that you also sort on Column A, so I added that to the sort routine — Ron Rosenfeld, Sep 11 '14 at 22:23
This is just about perfect. The issue is that the first row are the column names. The names are sorted in the initial sort, which goes on to mess up the subtotals. I changed the dim RG to `Set RG = WS.Range("a2").CurrentRegion` , but it's the same problem. — jabs, Sep 12 '14 at 13:08
@jabs We just need to add the Headers argument to the sort. I'll make the change in the code above — Ron Rosenfeld, Sep 12 '14 at 13:22
@jabs It is interesting that the sorting didn't affect the column names row in the sample file -- probably that's because in the samples, they serendipitously sorted to the top; whereas in your real data, that didn't happen. — Ron Rosenfeld, Sep 12 '14 at 13:35

score 0 · Answer 2 · answered Sep 11 '14 at 17:26

0

Replace "C" in "C2" with the column you want to sort on.

ActiveWorkbook.Worksheets("Sheet1").UsedRange.Sort key1:=Range("C2"), _
  order1:=xlAscending, header:=xlYes

Just sorts the whole sheet. You will get an error if the column at key1 does not exist, which makes pretty sense ;), so make sure it does.

answered Sep 11 '14 at 17:26

Roemer

1,124
8
23

In face, I will also want to use a subtotal on this, so it is important to get the last value. I've updated the question to reflect this. – jabs Sep 11 '14 at 17:46
1

What do you mean by "to get"? – Roemer Sep 11 '14 at 17:49
Get the row number of the last row. This way, I can plug it in to the sort and subtotal routines – jabs Sep 11 '14 at 17:56
"plug it in to the sort ans subtotal routines" -- you lost me. But the number of the last row is easy: `UsedRange.Rows.Count` gives that. – Roemer Sep 11 '14 at 18:03
I'm not explaining it well, so thanks for bearing with me. So, once I find the last row, how do I replace the hard-coded value that is in the code? i.e. how do replace .SetRange Range("A1:G6376") with .SetRange Range("A1:UsedRange.Rows.Count") – jabs Sep 11 '14 at 18:09
@PortlandRunner - I'm getting a syntax error. I added this to the top: `Dim lastRow As Range` `Set lastRow = Range("A100000").End(xlUp).Select` And this to the .SetRange `.SetRange Range("A1:G" & lastRow & ")` It errors out: Compile error: Syntax error on the SetRange line There seems to be an extra double quote(?) – jabs Sep 11 '14 at 18:49
Your returning a range instead of a row. Change to `Dim lastRow As Long` `Set lastRow = Range("A10000").End(xlUp).Row` and now you can use my previous code `Range("F2:F" & lastRow)` – Automate This Sep 11 '14 at 19:07
I changed it: `Dim lastRow As Long` `Set lastRow = Range("A100000").End(xlUp).Row` The second line still give the Compile Error: Object required on lastRow – jabs Sep 11 '14 at 19:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61076/discussion-between-jabs-and-portland-runner). – jabs Sep 11 '14 at 19:23
USedRange, as in my example, gives you all the range of the sheet exactly until the last row; so no need for to find the last row - unless you want to sort until the last row minus one. Try the code. And see if it works. And if not, you can always come back. It should work just fine. – Roemer Sep 11 '14 at 20:13

score 0 · Answer 3 · answered Sep 11 '14 at 20:24

UNTESTED

Try this for me.

Sub Sample()
    Dim thisWb As Workbook
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range

    Set thisWb = ThisWorkbook

    '~~> Set this to the relevant sheet
    Set ws = thisWb.Sheets("Sheet2")

    With ws
        '~~> Find the last Row. See the below link for more details
        '~~> http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                               After:=.Range("A1"), _
                               Lookat:=xlPart, _
                               LookIn:=xlFormulas, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlPrevious, _
                               MatchCase:=False).Row
        Else
            lRow = 1
        End If

        '~~> Set your range
        Set rng = .Range("A1:G" & lRow)

        With .Sort.SortFields
            .Clear

            .Add Key:=ws.Range("B2:B" & lRow), SortOn:=xlSortOnValues, _
                 Order:=xlAscending, DataOption:=xlSortNormal

            .Add Key:=ws.Range("D2:D" & lRow), SortOn:=xlSortOnValues, _
                 Order:=xlAscending, DataOption:=xlSortNormal

            .Add Key:=ws.Range("F2:F" & lRow), SortOn:=xlSortOnValues, _
                 Order:=xlAscending, DataOption:=xlSortNormal
        End With

        With .Sort
            .SetRange ws.Range("A1:G" & lRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

    '~~> Work with the range
    With rng
        .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

        .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True

        .Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    End With
End Sub

it didn't quite work, but it's close. It gave me 3 Grand Totals. I have a meeting now and will be off-line soon, but will continue looking later. I provide more details at that time. — jabs, Sep 11 '14 at 20:30
Sorry but it is 2 AM here. I am off to bed too :) You can post the sample workbook in any free file sharing site like www.wikisend.com and then share the link here. Use a macro recorder to produce the result that you want and then upload that file. I will take care of the rest — Siddharth Rout, Sep 11 '14 at 20:32
Thanks! The file lives [here](http://wikisend.com/download/488532/TestCase.xlsm). — jabs, Sep 11 '14 at 21:14
The file had the following actions applied: sort by B,D,F Then subtotal by A (with "replace current totals" checked), B (unchecked), D (unchecked). — jabs, Sep 11 '14 at 21:16

Excel VBA using Range in Macro

3 Answers3