PDA

View Full Version : Solved: sort columns dynamic



white_flag
02-26-2013, 04:06 AM
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
02-26-2013, 06:17 AM
The information you provide is rather 'scarce'....

white_flag
02-26-2013, 06:30 AM
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
02-26-2013, 07:49 AM
Why not posting an illustrative workbook ?

white_flag
02-26-2013, 07:57 AM
please look in attachment.

white_flag
02-26-2013, 08:49 AM
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
02-26-2013, 09:14 AM
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
02-27-2013, 01:20 AM
fantastisch
... heel hartelijk bedankt :)

white_flag
02-27-2013, 01:32 AM
een probleem
als ik een lege kolom tussen de kolommen. het script werkt niet

white_flag
02-27-2013, 01:36 AM
attachment

white_flag
02-27-2013, 02:28 AM
en als ik wil beginnen sorteren van de rij 5 bijvoorbeeld?

snb
02-27-2013, 02:38 AM
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
02-27-2013, 02:48 AM
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
02-27-2013, 09:41 AM
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