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

#### Andy15

##### Board Regular
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], _

End Sub``````

I would welcome any help and advice.

Many thanks
Andy

### 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
Your code is the same as this.

Code:
``````With Range("B:B")
With Range(.Cells(21, 1), .Cells(Rows,Count, 3).End(xlup)
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))
End With
End With

Next ColNum``````

#### Andy15

##### Board Regular
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
The dot in the For line should be omitted. For all other lines, it should be kept.

#### Andy15

##### Board Regular

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
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

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:= _
End With
End With

Next ColNum``````

#### Andy15

##### Board Regular
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

Last edited:

Replies
5
Views
61
Replies
5
Views
171
Replies
2
Views
156
Replies
2
Views
218
Replies
10
Views
202