Solved: sort columns dynamic

[white_flag]

Hello

I have 100 columns with data
It is possible to sort only column were the cursor is and the first column?
Then when you move from that column to restore the values and sort the values were the cursor is?

[snb]

The information you provide is rather 'scarce'....

[white_flag]

Hello snb

I try to say:

to sort the current column and column one to follow (only those 2).
col1_____colX
1________a
8________d
9________q
10________b

sort colX will result
col1_____colX
1________a
10_______b
8________d
9________q

When I move cursor to the right
the code will restore the preview column + the first one (on the previews state) and will sort the new one + first column


Private Sub sort()

Dim shName As String
Dim f_row As Integer, f_col, l_col, l_row

shName = "SortSheet"

f_row = ThisWorkbook.Worksheets(shName).UsedRange.Row
f_col = ThisWorkbook.Worksheets(shName).UsedRange.Column
l_col = ThisWorkbook.Worksheets(shName).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
l_row = ThisWorkbook.Worksheets(shName).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'f_row = 6

Range(Cells(f_row, f_col).Address, Cells(l_row, f_col).Address).Sort Key1:=Range(Cells(f_row, ActiveCell.Column).Address), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(Cells(f_row, ActiveCell.Column).Address, Cells(l_row, ActiveCell.Column).Address).Sort Key1:=Range(Cells(f_row, ActiveCell.Column).Address), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

[snb]

Why not posting an illustrative workbook ?

[white_flag]

please look in attachment.

[white_flag]

This is a good approach? Select the first column + the column were the cursor is ..then do the sorting ...



Option Explicit
Private Sub sort()
Dim shName As String
Dim f_row As Integer, f_col, l_col, l_row

shName = "SortSheet"

f_row = ThisWorkbook.Worksheets(shName).UsedRange.Row
f_col = ThisWorkbook.Worksheets(shName).UsedRange.Column
l_col = ThisWorkbook.Worksheets(shName).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
l_row = ThisWorkbook.Worksheets(shName).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

f_row = 2

Application.Union(Range(Cells(f_row, ActiveCell.Column).Address, Cells(l_row, ActiveCell.Column).Address), Range(Cells(f_row, f_col).Address, Cells(l_row, f_col).Address)).Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sort
End Sub

[snb]

Bedoel je dit ?


Sub M_snb()
If Not Intersect(Sheets("Sortsheet").Cells(1).CurrentRegion, ActiveCell) Is Nothing Then Sheets("Sortsheet").Cells(1).CurrentRegion.sort Sheets("sortsheet").Cells(1, ActiveCell.Column), , Sheets("sortsheet").Cells(1), , , , , xlYes
End Sub

[white_flag]

fantastisch
... heel hartelijk bedankt :)

[white_flag]

een probleem
als ik een lege kolom tussen de kolommen. het script werkt niet

[white_flag]

attachment

[white_flag]

en als ik wil beginnen sorteren van de rij 5 bijvoorbeeld?

[snb]

Please continue in the forum's language (English).
If you prefer to 'talk' Dutch you can try worksheet.nl

Since the sorting method uses '.currentregion' it assumes no empty rows/column in the area that has to be sorted.

If you want to sort the 5th column (and not the column in which the activecell resides) you can use:


Sub M_snb()
Sheets("Sortsheet").Cells(1).CurrentRegion.sort Sheets("sortsheet").Cells(1, 5), , Sheets("sortsheet").Cells(1), , , , , xlYes
End Sub

[white_flag]

Yes, you are correct.

anyway what do you think about this solution?


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
M_snb
End Sub
Sub M_snb()
Dim shName As String
Dim f_row As Integer, f_col, l_col, l_row

shName = "SortSheet"

f_row = ThisWorkbook.Worksheets(shName).UsedRange.Row
f_col = ThisWorkbook.Worksheets(shName).UsedRange.Column
l_col = ThisWorkbook.Worksheets(shName).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
l_row = ThisWorkbook.Worksheets(shName).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

f_row = 5

If Not Intersect(Sheets("Sortsheet").Range(Cells(f_row, f_col), Cells(l_row, l_col)), ActiveCell) Is Nothing Then _
Sheets("Sortsheet").Range(Cells(f_row, f_col), Cells(l_row, l_col)).sort Sheets("sortsheet").Cells(f_row, ActiveCell.Column), , Sheets("sortsheet").Cells(f_row), , , , , xlYes

End Sub

[snb]

I think it's too much code.
Please use Excel's builtin facilities first, before inventing what's already there:


Sub M_snb()
Sheets("Sortsheet").usedrange.sort Sheets("sortsheet").Cells(1, 5), , Sheets("sortsheet").Cells(1), , , , , xlYes
End Sub