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

[Andy15]

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.

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

 

[mikerickson]

Your code is the same as this.

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

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]

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]

The dot in the For line should be omitted. For all other lines, it should be kept.

 

[Andy15]

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]

With Range(.Cells(21, 1), .Cells(Rows[B][COLOR="#FF0000"].[/COLOR][/B]Count, 3).End(xlUp))

not

With Range(.Cells(21, 1), .Cells(Rows[COLOR="#FF0000"][B],[/B] [/COLOR]Count, 3).End(xlUp))

 

[Andy15]

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

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

 

[mikerickson]

crosspost
https://www.excelforum.com/excel-pr...next-3-columns-and-repeat-till-last-colu.html

 

[Andy15]

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

 

[mikerickson]

Posting on multiple forums is OK. Its failing to link to the other forums that is the issue.

Excelguru Help Site - A message to forum cross posters

As the link says, nobody is mad at you, but want you to succeed when you cross-post.