T
tinman
Hi,
I have the following macro set up for many columns in my spreadsheet
and it works fine. What I would like to do is simplify the sort
macro. I want to set up one universal sort macro rather than have one
for each column.
Codes below. Thanks in advance for any help.
tinman
*************************************************************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Me.Range("k14")) Is Nothing Then Exit Sub
'SORT
Dim LastCell As Range
Set LastCell = Range("k14").End(xlDown)
LastCell.Select
Range("k14").Select
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell > LastCell Then
Rows("15:579").Select
Selection.Sort Key1:=Range("k15"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Else
Rows("15:579").Select
Selection.Sort Key1:=Range("k15"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End If
'END SORT
On Error GoTo 0
End Sub
**********************************************************************************************
My attempt did not work very well.....................
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Me.Range("k14")) Is Nothing Then Exit Sub
Dim LastCell As Range
Set LastCell = Range(ActiveCell).End(xlDown)
LastCell.Select
Range(ActiveCell).Select
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell > LastCell Then
Rows("15:579").Select
Selection.Sort Key1:=Range(ActiveCell.Offset(1, 0)),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Else
Rows("15:579").Select
Selection.Sort Key1:=Range(ActiveCell.Offset(1, 0)),
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End If
On Error GoTo 0
End Sub
***********************************************************************************************
I have the following macro set up for many columns in my spreadsheet
and it works fine. What I would like to do is simplify the sort
macro. I want to set up one universal sort macro rather than have one
for each column.
Codes below. Thanks in advance for any help.
tinman
*************************************************************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Me.Range("k14")) Is Nothing Then Exit Sub
'SORT
Dim LastCell As Range
Set LastCell = Range("k14").End(xlDown)
LastCell.Select
Range("k14").Select
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell > LastCell Then
Rows("15:579").Select
Selection.Sort Key1:=Range("k15"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Else
Rows("15:579").Select
Selection.Sort Key1:=Range("k15"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End If
'END SORT
On Error GoTo 0
End Sub
**********************************************************************************************
My attempt did not work very well.....................
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Me.Range("k14")) Is Nothing Then Exit Sub
Dim LastCell As Range
Set LastCell = Range(ActiveCell).End(xlDown)
LastCell.Select
Range(ActiveCell).Select
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell > LastCell Then
Rows("15:579").Select
Selection.Sort Key1:=Range(ActiveCell.Offset(1, 0)),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Else
Rows("15:579").Select
Selection.Sort Key1:=Range(ActiveCell.Offset(1, 0)),
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End If
On Error GoTo 0
End Sub
***********************************************************************************************