Sorting selected columns by using VBA

[Manster]

Hi expert,

Sorry, I'm very novice in VBA. I have a problem regarding the sorting issue. Is it possible to sort selected columns by using VBA?

Example: Once we click a button (in excel),
A1:A10 --> Sorted accordingly from A to Z
B1:B10 --> Not Sorted, it still maintain the values (This what I need to know how to maintain this values in the cell columns).
C1:C10 --> Sorted accordingly from A to Z
D1:D10 --> Sorted accordingly from A to Z

I hope The Expert in this forum can help me. Thanks in advance!

 

[Chihiro]

You mean each range is independent from each other and sort on itself only?

You can do something like below to sort each column independently and not sort column B (col 2).

Sub Test()
Dim i As Integer
Dim col As Range

For Each col In Range("A1:D10").Columns
    If Not col.Column = 2 Then
        col.Sort Key1:=col.Cells(1), Order1:=xlAscending, _
        Header:=xlNo
    End If
Next

End Sub

 

[Manster]

Hi Chihiro,

Thanks response my thread. Basically when we are sorting in excel;

Once we click a button (in excel by create VBA),
A1:A10 --> Sorted accordingly from A to Z; A column is Student Name
B1:B10 --> Sorted accordingly from A to Z; B column is Student Class
C1:C10 --> Sorted accordingly from A to Z; C column is Matric ID
D1:D10 --> Sorted accordingly from A to Z; D column is Carry Marks

*Reference is Student Name for example.

But, is it possible we can maintain the column student class, in other words, all columns (refer example above) sort out except B1:B10?

 

[Chihiro]

Added code to post above. Should do what you are looking for.

 

[Manster]

Hi Sir,

Thank you about the command you give it to me and it really works! Sir, can you check my VBA (Actually, i'm not created from scratch, I just copy it from other files & altered it).

Sub ClickButton()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("AT")

lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set sorta = ws.Range("E13:N38")
Application.ScreenUpdating = False

ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("E14:E38"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ws.Sort.SortFields.Add Key:=Range("G14:G38"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ws.Sort.SortFields.Add Key:=Range("J14:J38"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ws.Sort
.SetRange sorta
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("E12").Select

Application.ScreenUpdating = True

End Sub


My problem is, how can I 'deny' "F14:F38" & "H14:H38" from the sorting together upon I click a button?

 

[Chihiro]

Basically, replace "ws" part of code to specific range. When you use "ws" for sort, it sorts entire range as one, instead on individual columns.

Something like below for single column.

Range("E13:E38").Sort Key1:=Range("E13"), Order1:=xlAscending, Header:=xlYes

Or something like below... to loop through header range and sort on column when .Column index matches certain # (Ex: A=1, E=5 etc)

Sub ClickButton()
Dim ws As Worksheet
Dim col As Range
Dim lr As Integer

Set ws = ThisWorkbook.Worksheets("AT")

lr = ws.Cells(Rows.Count, 5).End(xlUp).Row

Application.ScreenUpdating = False


ws.Sort.SortFields.Clear

For Each col In Range("E13:N13")
  
    Select Case col.Column
    Case Is = 5, 7
        col.Resize(lr, 1).Sort Key1:=col, Order1:=xlAscending, Header:=xlYes
    Case Is = 10
        col.Resize(lr, 1).Sort Key1:=col, Order1:=xlDescending, Header:=xlYes
    End Select
  
Next

Range("E12").Select

Application.ScreenUpdating = True

End Sub

However, I'm not sure that you are trying to accomplish in below part.

With ws.Sort
.SetRange sorta
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[Manster]

Sir, I already run your coding above. That's what I need actually. However, I facing another problem, "to do this all the merged cells need to be the same size".
Ya, I admit that my cells are merged. What should I change that code to suit the cells that i merged already?

 

[Chihiro]

Merged cell = Evil. Don't use it, ever.

But if it can't be avoided for some reason (like your boss threatening to fire you if you didn't)... then you'd need to code in portion to un-merge then re-merge cells. Can you upload sample file matching exact set up of your actual workbook?

 

[Manster]

Sir, sorry for the late reply. Kindly please find an attachment for your kind attention.

 

[Chihiro]

In your set up, there's no reason for merged cells...

But, you can change code to something like below.

Demo for first merged column range (Col C:G) only.

Sub SortMatric()
Dim ws As Worksheet
Dim col As Range
Dim lr As Integer

Set ws = ThisWorkbook.Worksheets("ATTENDANCE")

lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False


ws.Sort.SortFields.Clear

For Each col In Range("C14:AV14")
 
    Select Case col.Column
    Case Is = 3
        col.Resize(lr - 13, 5).Sort Key1:=col, Order1:=xlAscending, Header:=xlNo
    End Select
 
Next

Application.ScreenUpdating = True


End Sub

This confines sort range to data range with same merged cell size only.

Change list:
1. Range
Notice that it now changed to first row of data (row 14)
2. Resize(lr - 13, 5)
Row range is confined to data range (row 14 to 38), and column range expanded from 1 to 5 (# of merged column).
3. Header:=xlNo
Exclude header from consideration since sort range is now row 14 to 38.