Select 1st 2d range, sort, find 2nd, sort, etc

**finny388** · 12-22-2010, 01:39 AM

See attached.

I want to sort each group (there are 3 groups in the sheet) and sort each by F:F, no headers.

In the sheet 2 I show the desired result

So in the example sheet, sort D5:l5 by F:F no headers, asc order
Then sort D9:L11 by F:F no headers, asc order
Then sort D15:L20 by F:F no headers, asc order

I have sheets like this with 50 orders and I have to select and sort each.

How can a macro do that?

**antoka05** · 12-22-2010, 06:42 AM

You could use this macro I tested on your 'result' sheet:

SSub Macro1()
   Dim columnsToSort As String
   Dim firstCol As String, colForSort As String
   Dim lastRow As Long, lastCol As String
   Dim startRow As Long, endRow As Long
   Dim r As Integer

   'parameter to set
   columnsToSort = "d:m"
   colForSort = "f"
   
   firstCol = Split(columnsToSort, ":")(0)
   lastCol = Split(columnsToSort, ":")(1)
   With ThisWorkbook.ActiveSheet
      lastRow = .Cells.Find("*", SearchOrder:=xlByRows, _
           SearchDirection:=xlPrevious).Row
    
      For r = 5 To lastRow + 1
         If IsNumeric(.Cells(r, "e")) Then
            If startRow = 0 Then
               startRow = r
            End If
         ElseIf startRow > 0 Then
            endRow = r - 1
            'sort data
            If endRow > startRow Then
                .Range(firstCol & startRow & ":" & lastCol & endRow).Sort _
                   Key1:=.Range(colForSort & startRow), Order1:=xlAscending, _
                   Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
                   Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
            End If
            
            startRow = 0
         End If
      Next r
   End With
End Sub

Regards,
Antonio

**watersev** · 12-22-2010, 09:43 AM

much shorter option of the code, see attachment, run macro "sort_me"

Sub sort_me()
Dim a, i As Long
Application.ScreenUpdating = False: On Error GoTo Handler
a = Split([e:e].SpecialCells(xlCellTypeConstants, 1).Address, ",")
For i = 0 To UBound(a)
Range(a(i)).Resize(Range(a(i)).Rows.Count, 8).Sort Key1:=Range(Split(a(i), ":")(0)).Offset(0, 1), Order1:=xlAscending
Next: Application.ScreenUpdating = True: On Error GoTo 0
Exit Sub
Handler:
On Error GoTo 0: Exit Sub
End Sub

**finny388** · 12-22-2010, 05:50 PM

wow watersev, that seems to work like a charm

I'm trying to learn vba and the code is so short - would you be willing to describe how that works?

very impressed with how compact that is

also thank you antoka for your post

**watersev** · 12-22-2010, 06:31 PM

some explanation:
1. Column "E": looking for cells with digits (analog action select column "E" and press [Ctrl+G] choose option for constants, digits). That gives us all ranges separated by coma,
2. Parsing all those ranges by Split command and getting them in array
3. Loop through array of ranges selecting range and sorting as required
4. To avoid error message when cells with digits inside are absent - On error Goto Handler. Handler contains appropriate actions
If you do study VB it would be useful to go through all commands of the code in VB help: [Alt+F11] in open Excel workbook

**finny388** · 12-22-2010, 06:59 PM

Thank you

1 I put a Watch on a (Dim a as Long) but it never had a value?
2 I would like to evaluate f:f for digits instead of e:e, is that possible with this code and still have e:e in the array? originally I waned to sort from column D to L actually

**watersev** · 12-23-2010, 03:26 AM

1. Variable "a" is variant, it gets value after split
2. Yes, it's possible to do the same trick with [f:f] with some code changes to follow as well. What do you mean "by sort from column d to l actually"? Do you want to sort appropriate range from d column to l column by which column?

**finny388** · 06-22-2011, 05:12 PM

Hi Watersev,

I am doing this again.

a = Split([e:e].SpecialCells(xlCellTypeConstants, 1).Address, ",")

My sheet as over 200 groups of Constants, 1's.

But Split stops at 18.

If I ctrl-G and choose Constants, Numbers it selects all 200+ groups.

Any ideas?

Sorry, this post is old, but you were so helpful.

**watersev** · 06-22-2011, 05:18 PM

any chance to see workbook sample?

**finny388** · 06-22-2011, 05:59 PM

find file attached

**finny388** · 06-22-2011, 06:03 PM

e243:e263 is the last range in the array

**watersev** · 06-23-2011, 06:02 AM

please check attachment, run code "sort me"

**finny388** · 06-24-2011, 02:45 PM

thanks watersev

what made you switch from loading an array to using the Areas property?

you're so talented

**watersev** · 06-24-2011, 03:12 PM

you did ..

... areas address is very long ... looping through areas works fine

Select 1st 2d range, sort, find 2nd, sort, etc

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Re: Select 1st 2d range, sort, find 2nd, sort, etc

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions