Sort columns B,C,D based on column B then loop to next 3 columns and repeat till last column

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56
Hi Guy's I am looking for a little bit of help if possible.

I have a worksheet that contain numerous columns of data that has been imported from other worksheets.

The columns start at column B and repeat every 3 columns. The number of columns can vary per worksheet.

The number of rows can also vary within the blocks of 3 columns. (e.g. columns B,C D may have 20 rows. columns E,F,G may have 27 rows etc.)

I have managed to sort the first 3 columns using the code below, but can not work out how to loop through to the next 3 columns and so on until the last column.

Code:
Sub sort1()


Range("B21", Range("D" & Rows.Count).End(xlUp).Address).sort Key1:=[b21], _
    Order1:=xlAscending, Header:=xlYes
    
End Sub


I would welcome any help and advice.

Many thanks
Andy
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,838
Your code is the same as this.

Code:
With Range("B:B")
    With Range(.Cells(21, 1), .Cells(Rows,Count, 3).End(xlup)
        .Sort Key1:=.Cells(21, 1), Order1:=xlAscending, Header:=xlYes
    End With
End With

Notice that the outer With is the only place that references the column, everything inside is relative to that column.

So you can use this loop

Code:
Dim ColNum As Long

For ColNum = 2 to .Cells(21, Columns.Count).End(xlToLeft).Column Step 3

    With Columns(ColNum)
        With Range(.Cells(21, 1), .Cells(Rows,Count, 3).End(xlup))
            .Sort Key1:=.Cells(21, 1), Order1:=xlAscending, Header:=xlYes
        End With
    End With

Next ColNum
 

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56
Hi

Thanks for you reply, I have tried your code but I get an invalid or unqualified reference and it is pointing to .Cells

Thanks
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,838
The dot in the For line should be omitted. For all other lines, it should be kept.
 

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56

ADVERTISEMENT

Hi,

I then receive a wrong number of arguments error 450

highlighting this line

With Range(.Cells(21, 1), .Cells(Rows, Count, 3).End(xlUp))
Thanks<style type="text/css">p.p1 {margin: 5.0px 0.0px 5.0px 0.0px; font: 12.0px 'Times New Roman'}</style>
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,619
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Code:
With Range(.Cells(21, 1), .Cells(Rows[B][COLOR="#FF0000"].[/COLOR][/B]Count, 3).End(xlUp))
not
Code:
With Range(.Cells(21, 1), .Cells(Rows[COLOR="#FF0000"][B],[/B] [/COLOR]Count, 3).End(xlUp))
 

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56

ADVERTISEMENT

Hi Mark858,

Thanks for your input, that works great. Can you tell me how to add a customOrder:- to the above as I keep getting named argument not found

Code:
Dim ColNum As Long

For ColNum = 2 To Cells(21, Columns.Count).End(xlToLeft).Column Step 3


    With Columns(ColNum)
        With Range(.Cells(21, 1), .Cells(Rows.Count, 3).End(xlUp))
            .sort Key1:=.Cells(21, 1), Order1:=xlAscending, CustomOrder:= _
        "10115960,902240,11241290,10080910,11241460,10237680,11241500,10818620,10005390,10237710,11242600,11241480,11241470,11241589,11241599,11241649,11241539,11241529,11241609,11247349,11241210,10774620,11241220,11241230,11241202", Header:=xlYes
        End With
    End With


Next ColNum
 

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56
Hi mikerickson,

Sorry for cross posting but I didn't know what it meant. After reading the reasons why I now understand the importance of not cross posting.

Andy15
 

Watch MrExcel Video

Forum statistics

Threads
1,118,568
Messages
5,572,974
Members
412,491
Latest member
tweetytoon
Top