SelectionChange/ toggle sort visible rows

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
***********************************************************************************************
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top