VBA Sort Col's Independent of One Another

[RLCornish]

Hello,

I'm trying to figure out how to sort columns independently of one another using VBA, and I have a good little piece of code that does this for me (see below). However, I've run into a couple of other things.

1. Some columns may need a custom sort order, not just ascending. What does something like this look like?
2. I have 2 rows at the top, not just 1, that need to not be included in the sort. My code is working perfectly to exclude row 1, but grabs row 2 in the sort and I need it not to. Any ideas how to accomplish this?

It seems like this should be so easy, but I've played enough to know I don't know where I need to head with this. Help please!

Here's the code I have...copied from ozgrid

Sub SortColumsIndividually()
Dim rCell As Range

For Each rCell In Range("A1:D1") 'Change range to suit
rCell.EntireColumn.Sort Key1:=rCell(2, 1), _
Order1:=xlAscending, Header:=xlYes
Next rCell

End Sub

 

[RickXL]

Hi,

I think this works:

Sub SortColumsIndividually()
    Dim rCell As Range
    
    For Each rCell In Range("A1:D1") 'Change range to suit
        rCell.EntireColumn.Resize(Rows.Count - 1).Offset(1).Sort Key1:=rCell.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
    Next rCell
End Sub

The problem was the usage of EntireColumn. Using that made the VBA ignore the start row you entered anywhere else.

rCells.Cells(1,1) is really there just to set the column so changing the row there makes no difference.

What I have done is added an Offset of 1 row to move the starting point down a row and then Resized the column to make sure that it still finishes at row 1048756 (in my version of Excel).

To start a row further down you would use:

rCell.EntireColumn.Resize(Rows.Count - 2).Offset(2).Sort Key1:=rCell.Cells(1, 1), Order1:=xlAscending, Header:=xlYes

I hope that makes sense.


Regards,