Excel - Sorting Data by VBA

[Binnit]

I have the following code (which isn't working) the problem is in the Range aspect, basically I want to start the sort from Row 4 to Row 100 for each Column using a For Next Loop

X = ActiveSheet.UsedRange.Columns.Count
[a1].Select

For i = 1 To X

Selection.Sort Key1:=Range(Cells(4, i), Cells(100, i)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
Next i

Can someone tell me why this code does not work... it puts the contents of the cells in row 4 up to cell A1

Thanks



If IT ain’t working Binnit and Reboot

 

[SkipVought]

Hi,

You're making this ENTIRELY too difficult.

Hint: Turn on your macro recorder and envoke the sort feature. Then observe the generated code.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Binnit]

Skip
Thanks, I did this originally but rather than hardcoding each range to sort, I wanted to create the For/Next loop to do the same for each column starting at row 4.

My problem seems to be integrating the loop variable in to the sort range.

Any further clues?


If IT ain’t working Binnit and Reboot

 

[SkipVought]

So if you have 10 coloumns, you'll do 10 sorts?

That's OK. However, be aware that you must sort from the LEAST significant column to the MOST significant column in order to achieve a proper sort result.

    With ActiveSheet
        With .UsedRange
            X = .Columns.Count
            r = .Row
        End With
        
        For i = 1 To X
        
            .[A1].Sort _
                Key1:=.Range(.Cells(r, i), .Cells(r, i)), _
                Order1:=xlAscending, _
                Header:=xlNo, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom
                
        Next i
    End With

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Binnit]

Skip
I don't need to do the significant sorts, just sort each column according to its own data, however, your code above looks like the syntax's I'm looking for so I will give it a blast and post back.

Thanks


If IT ain’t working Binnit and Reboot

 

[Binnit]

Skip
Your code does the same as mine previously (although it does look better)!

In my sheet, I have 3 header rows, the 3rd is the actual header to be used for the Sort, each column has a minimum of 5 row entries but can have up to 100.

The same problem still exists, it takes the contents of all the cells in Row 4 and dumps them at Row 1

Something is not quite right.



If IT ain’t working Binnit and Reboot

 

[SkipVought]

So you're saying that each column is sorted ALONE without the other columns being sorted, cuz that's NOT what will happen.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Binnit]

p.s
This is what the original macro recording gave me:-

Range("c3:c100").Select
Selection.Sort Key1:=Range("c4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
Range("d3:d100").Select
Selection.Sort Key1:=Range("d4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Its the range bit that I need to build in to the For/Next loop


If IT ain’t working Binnit and Reboot

 

[SkipVought]

To do what you seem to want to do...

    With ActiveSheet
        With .UsedRange
            c1 = .Column
            c2 = c1 + .Columns.Count - 1
            r1 = .Row
            r2 = r1 + .Rows.Count - 1
        End With
        
        For i = c1 To c2
        
            .Range(.Cells(r1, i), .Cells(r2, i)).Sort _
                Key1:=.Cells(r1, i), _
                Order1:=xlAscending, _
                Header:=xlNo, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom
                
        Next i
    End With

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Binnit]

Skip

Oooh err, its getting worse... this time it moved all the header values and dumped them around the used range (I cannot see any obvious pattern behind it)

What I think I want is to replace

Range("d3:d100").Select (with the loop range)
and
Key1:=Range("c4") with the loop variable

this to apply to the used range as before


If IT ain’t working Binnit and Reboot

 

[SkipVought]

You specified

Header:=xlNo

My test worked with NO HEADERS!

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Binnit]

Skip
OK, Im probably getting myself in a twist here....

I have just found something about recording macros with Relative cells and when doing a new recording it has produced the following method:-

I will try it and see if it helps.



If IT ain’t working Binnit and Reboot

 

[Binnit]

OK

Range("A3:A100").Select
    Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    ActiveCell.Offset(0, 1).Range("A1:A98").Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    ActiveCell.Offset(-1, 1).Range("A1:A98").Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

The first case was using non relative cells on the macro recorder the 2 others used the relative cells option.

I will try to work it out from here!
Thanks for your help Skip

If IT ain’t working Binnit and Reboot

 

[Binnit]

Skip
The following is now working but still includes hardcoded ranges which I was trying to avoid.

R = ActiveSheet.UsedRange.Rows.Count
C = ActiveSheet.UsedRange.Columns.Count

[a1].Select

Range("A3:A100").Select
For i = 1 To C
    
    ActiveCell.Offset(0, 1).Range("A1:A98").Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
Next i

At least it now works which is the main thing!

Thanks for your help


If IT ain’t working Binnit and Reboot

 

[Binnit]

Skip
Actually the previous code did not work as I thought it was, I have now got this sussed as follows!

Sub SortData()
C = ActiveSheet.UsedRange.Columns.Count

    [a1].Select
    ActiveCell.Offset(3, 0).Range("A1:A98").Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
For i = 1 To C
        
    ActiveCell.Offset(0, 1).Range("A1:A98").Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Next i
[a1].Select
End Sub

The first part sets the position of the first sort
The 2nd part then moves the active cel reference 1 column to the right and then sorts that etc.

Job done but thought it would have been easier!


If IT ain’t working Binnit and Reboot

 

[SkipVought]

FYI:

1. I would never recommend designing independent list to be in adjacent cells. Each list ought to be totally isolated from other data (including extraneous heading data such as in the first 4 rows as you described)

2. I advise against the use of the Select and Activate methods for the purpose of range referencing in all but a few circumstances (such as conditional formatting)

How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Binnit]

Skip
Your comments noted and generally agreed, in this case, we have a list of users that belong to 1 or more security groups, for "visual" effect only these are listed in 15 columns.

The sorting procedure was a way of tidying the columns up after adding or deleting names without having to do each column independantly.

Thanks for your tips

If IT ain’t working Binnit and Reboot