UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Sort Range To End Of Data - Relative Reference / Macro, Office 2010    
 
   
bakersburg9
post Apr 20 2012, 07:00 PM
Post #1

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
I have a workbook that I'm adding rows to and after each time I add a row, I want a fresh sort, alphabetically, on Column A to do it manually, with the v2010 sort btns, it works great - just click the sort button, accept the default choices, hit enter, hit enter ... but this is a big project, and it's frustrating - I'm using macro recorder to automate this process, but it always grabs the absolute cells references, even if I turn the 'Relative reference' btn ON -

Like here - my data range ends at cell G349 - so this works, but when I add my next row, it's going to end at G350, and my macro won't work - what code to I use to go to the full range of data, even dynamically, as it changes ?

CODE
    ActiveWorkbook.Worksheets("MyWorksheetName").SORT.SortFields.Add Key _
        :=Range("A2:A349"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("MyWorksheetName").SORT
        .SetRange Range("A1:G349")


any help would be greatly appreciated !
Go to the top of the page
 
+
norie
post Apr 20 2012, 07:19 PM
Post #2

UtterAccess VIP
Posts: 4,295
Try defining the range to sort first.
CODE
Dim rngSort As Range

    With ActiveWorkbook.Worksheets("MyWorksheetName")
        Set rngSort = .Range("A2", .Range("A" & Rows.Count).End(xlUp))

        .Sort.SortFields.Add Key _
                             :=rngSort, SortOn:=xlSortOnValues, Order:=xlAscending, _
                             DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("MyWorksheetName").Sort
            .SetRange rngSort
            .Apply
        End With
    End With
Go to the top of the page
 
+
bakersburg9
post Apr 21 2012, 11:52 AM
Post #3

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
norie,
Thanks !
Steve

(IMG:style_emoticons/default/cool.gif)
Go to the top of the page
 
+
bakersburg9
post Apr 23 2012, 10:20 AM
Post #4

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
QUOTE (norie @ Apr 21 2012, 12:19 AM) *
Try defining the range to sort first.
CODE
Dim rngSort As Range

    With ActiveWorkbook.Worksheets("MyWorksheetName")
        Set rngSort = .Range("A2", .Range("A" & Rows.Count).End(xlUp))

        .Sort.SortFields.Add Key _
                             :=rngSort, SortOn:=xlSortOnValues, Order:=xlAscending, _
                             DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("MyWorksheetName").Sort
            .SetRange rngSort
            .Apply
        End With
    End With


Norie, I spoke to soon - all that did was sort the new data in column a - plus, I might not even have any values in column a - I wanted to select the entire range, based on the first cell (A2) to the last cell say the last row of data was on row 349, and I added something in cell c350 and cell g350, I'd want the sort range to be A2 - G350 - your macro just sorts column a by itself, independent of the other data
Go to the top of the page
 
+
norie
post Apr 23 2012, 11:44 AM
Post #5

UtterAccess VIP
Posts: 4,295
Try this.
CODE
Dim rngSort As Range


    With ActiveWorkbook.Worksheets("Sheet1")
        Set rngSort = .Range("A2", .Range("G" & Rows.Count).End(xlUp))
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key _
                             :=rngSort.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, _
                             DataOption:=xlSortNormal
        With .Sort
            .SetRange rngSort
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

Note I've assumed a couple of things - there are no headers, and you want to sort top to bottom ascending.
Go to the top of the page
 
+
bakersburg9
post Apr 23 2012, 11:54 AM
Post #6

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
QUOTE (norie @ Apr 23 2012, 04:44 PM) *
Try this.
CODE
Dim rngSort As Range


    With ActiveWorkbook.Worksheets("Sheet1")
        Set rngSort = .Range("A2", .Range("G" & Rows.Count).End(xlUp))
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key _
                             :=rngSort.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, _
                             DataOption:=xlSortNormal
        With .Sort
            .SetRange rngSort
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

Note I've assumed a couple of things - there are no headers, and you want to sort top to bottom ascending.

didn't work sorry - I typed some dummy data in cell c350 and g350 - and nothing happened - thx anyway
Go to the top of the page
 
+
norie
post Apr 23 2012, 12:00 PM
Post #7

UtterAccess VIP
Posts: 4,295
You only have data in 2 cells but you want to sort 7 columns?(IMG:style_emoticons/default/dazed.gif)

Perhaps you could attach a workbook with some sample data?
Go to the top of the page
 
+
bakersburg9
post Apr 23 2012, 01:08 PM
Post #8

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
QUOTE (norie @ Apr 23 2012, 05:00 PM) *
attach a workbook with some sample data


here you go - still not working - and I do have headers - maybe that's the issue ?

This post has been edited by bakersburg9: Apr 23 2012, 01:09 PM
Attached File(s)
Attached File  Sort_Range_Challenge.zip ( 85.83K ) Number of downloads: 1
 
Go to the top of the page
 
+
norie
post Apr 23 2012, 01:16 PM
Post #9

UtterAccess VIP
Posts: 4,295
Which worksheet is to be sorted?
Go to the top of the page
 
+
bakersburg9
post Apr 23 2012, 01:29 PM
Post #10

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
QUOTE (norie @ Apr 23 2012, 06:16 PM) *
Which worksheet is to be sorted?

?? There's only one - sheet1
Go to the top of the page
 
+
norie
post Apr 23 2012, 01:33 PM
Post #11

UtterAccess VIP
Posts: 4,295
There's 3 worksheets.
Go to the top of the page
 
+
bakersburg9
post Apr 23 2012, 01:37 PM
Post #12

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
Norie,
My bad - sorry - had one save to my Documents, one to desktop - picked the wrong one - here you go
Attached File(s)
Attached File  Sort_Range_Challenge_New.zip ( 36.97K ) Number of downloads: 0
 
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th May 2013 - 09:25 AM