Wow .....you guys are great!!!
I've stumbled upon a sorting code at :
www.clearlyandsimply.com/clearly_and_simply/2011/05/sort-excel-tables-by-double-clicking.html
Here is the code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim rngTable As Range
Dim rngActiveColumn As Range
Dim rngOneCell As Range
Dim intSortOrder As Integer
Dim blnNumericCol As Boolean
Dim intExistingSortOrder As Integer '0: unsorted, 1: ascending, 2:
descending
Dim intNewSortOrder As Integer
Dim strFormula1 As String
Dim strFormula2 As String
On Error Resume Next
' Exit sub if double click outside of defined table range name
"myDataTable"
If Application.Intersect(ActiveCell, Range("myData").Cells) Is
Nothing Then Exit Sub
Application.ScreenUpdating = False
' Define the ranges of the table and of the active column
Set rngTable = Range("myData")
Set rngActiveColumn = _
Range("myData").Cells(2, ActiveCell.Column -
Range("myData").Cells(1, 1).Column +
1).Resize(Range("myData").Rows.Count - 1, 1)
' Check if the active column contains numbers or alphanumeric data
blnNumericCol = True
For Each rngOneCell In rngActiveColumn
If Not IsNumeric(rngOneCell) Then
blnNumericCol = False
Exit For
End If
Next rngOneCell
' Check if the column is sorted and detect the existing sort order
(create array formula strings to be evaluated)
strFormula1 = "AND(" &
rngActiveColumn.Resize(rngActiveColumn.Rows.Count - 1, 1).Address &
">=" & _
rngActiveColumn.Resize(rngActiveColumn.Rows.Count
- 1, 1).Offset(1, 0).Address & ")"
strFormula2 = "AND(" &
rngActiveColumn.Resize(rngActiveColumn.Rows.Count - 1, 1).Address &
"<=" & _
rngActiveColumn.Resize(rngActiveColumn.Rows.Count
- 1, 1).Offset(1, 0).Address & ")"
If Evaluate(strFormula1) Then
intExistingSortOrder = 2
ElseIf Evaluate(strFormula2) Then
intExistingSortOrder = 1
Else
intExistingSortOrder = 0
End If
' Set the new sort order
Select Case intExistingSortOrder
Case 0:
If blnNumericCol Then
intNewSortOrder = xlDescending
Else
intNewSortOrder = xlAscending
End If
Case 1: intNewSortOrder = xlDescending
Case 2: intNewSortOrder = xlAscending
End Select
' Sort the table
rngTable.Offset(1, 0).Sort Key1:=Cells(rngTable.Row + 1,
ActiveCell.Column), Order1:=intNewSortOrder, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' Optional: Set the fill color of the sorted column to a light
grey
rngTable.Offset(1, 0).Resize(rngTable.Rows.Count - 1,
rngTable.Columns.Count).Interior.ColorIndex = xlNone
rngActiveColumn.Interior.Color = RGB(234, 234, 234)
' Clean up
Set rngTable = Nothing
Set rngActiveColumn = Nothing
Set rngOneCell = Nothing
Application.ScreenUpdating = True
End Sub
You need only 2 simple steps to transfer this feature to your own
workbook:
Assign the range name “myData” to your table (including the headers of
the columns).
Go to the VBE (ALT-F11) and copy and paste the code from the workbook
provided above into the sheet module of the worksheet containing the
data table.
Regards.
On Aug 28, 2:52 am, rajan verma <rajanverma1...@gmail.com> wrote:
> Great Noorain!!
>
> Does anybody have more method for sorting..
>
> On Sat, Aug 27, 2011 at 9:06 PM, NOORAIN ANSARI
> <noorain.ans...@gmail.com>wrote:
>
>
>
>
>
>
>
>
>
> > Hi Group,
>
> > I have added some extra sorting tips in rajan's Sorting tips.
> > Like
> > Sorting through Vlookup/Hlookup
> > Sorting Through Sumporduct
>
> > Vertical and Horizental both sorting attached herewith.
>
> > On Sat, Aug 27, 2011 at 6:48 PM, Rajan_Verma
> > <rajanverma1...@gmail.com>wrote:
>
> >> Hi Group,****
>
> >> Refer the attached sheet with some Data Sorting Example. Hope you will
> >> like it****
>
> >> ** **
>
> >> Rajan.****
>
> >> ** **
>
> >> --
>
> >> ---------------------------------------------------------------------------
> >> -------
> >> Some important links for excel users:
> >> 1. Follow us on TWITTER for tips tricks and links :
> >>http://twitter.com/exceldailytip
> >> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >> To post to this group, send email to excel-macros@googlegroups.com
>
> >> <><><><><><><><><><><><><><><><><><><><><><>
> >> Like our page on facebook , Just follow below link
> >>http://www.facebook.com/discussexcel
>
> > --
> > Thanks & regards,
> > Noorain Ansari
> > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>
> > --
>
> > ---------------------------------------------------------------------------
> > -------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> Regards
> Rajan verma
> +91 9158998701
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
