How to Sort my worksheet in ascending order?

[ianccy]

How to sort my worksheet in ascending order? and how about descending order?

Thanks

 

[gnaga]

Do you really need the answer for this question? I doubt because you have posted 66 times in this board, so by this time you should have learnt the sorting feature even if you have started as dummy in excel.

GNaga

 

[stevebausch]

Yep, I concur; how stupid could you be?

Just in case, though, here is the answer:

Data/Sort on menu

set the buttons for "ascending"

 

[ThePencilQueen]

It is a bit worrying that someone can handle vlookups but not sorting.

 

[Richie(UK)]

Just a thought ... did you mean sorting the contents of a worksheet (which has been well covered above :wink: )or did you mean sorting the worksheets within a workbook by their sheet name?

In case its the latter, have a look at this code (from EDC I think):

<pre>
Sub SortSheets()
' This routine sorts the sheets of the
' active workbook in ascending order.

Dim SheetNames() As String
Dim i As Integer
Dim SheetCount As Integer
Dim VisibleWins As Integer
Dim Item As Object
Dim OldActive As Object

' Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is protected.", _
vbCritical, "Cannot Sort Sheets"
Exit Sub
End If

' Disable Ctrl+Break
Application.EnableCancelKey = xlDisabled

' Exit if no windows are visible
VisibleWins = 0
For Each Item In Windows
If Item.Visible Then VisibleWins = VisibleWins + 1
Next Item
If VisibleWins = 0 Then Exit Sub

' Get the number of sheets
SheetCount = ActiveWorkbook.Sheets.count

' Redimension the array
ReDim SheetNames(1 To SheetCount)

' Store a reference to the active sheet
Set OldActive = ActiveSheet

' Fill array with sheet names and hidden status
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i

' Sort the array in ascending order
Call BubbleSort(SheetNames)

' Turn off screen updating
Application.ScreenUpdating = False

' Move the sheets
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
ActiveWorkbook.Sheets(i)
Next i

' Reactivate the original active sheet
OldActive.Activate
End Sub

Sub BubbleSort(List() As String)
'‘ Sorts the List array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim temp

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If UCase(List(i)) > UCase(List(j)) Then
temp = List(j)
List(j) = List(i)
List(i) = temp
End If
Next j
Next i
End Sub</pre>

HTH

 

[ianccy]

I know how to sort cells in a worksheet, that is easy, but not in sorting 'many worksheets name', Thanks for the help

 

[SamS]

Ian, I read it the way you proposed (I suppose after you've had 600+ posts you get a bit lazy in reading ) but was unable to help you but I see Richie was able to help.

 

[RolanDoobies]

I was looking at the work Richie did here, and dont understand why the second piece of code doesnt show up in my macro list. The one titled bubble sort. Since it doesnt show up, im assuming its tied to the first one. Can someone explain how?

 

[Iridium]

Check this out:

Sub WorksheetsSortAscending()
'http://www.erlandsendata.no/english/vba/wb/sortsheets.htm
' sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
    Application.ScreenUpdating = False
    sCount = Worksheets.Count
    If sCount = 1 Then Exit Sub
    For i = 1 To sCount - 1
        For j = i + 1 To sCount
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move before:=Worksheets(i)
            End If
        Next j
    Next i
End Sub

HTH

Edit: assuming you meant sort the worksheets rather than each sheets contents. btw I think stevebausch and gnaga could have been a bit less harsh in their responses however well intentioned they were!

 

[Richie(UK)]

Hi RD,

The reason it doesn't show up is that you can't directly run it.

If you look at the first line:

Sub BubbleSort(List() As String)

you will see that the routine takes an argument (List). You therefore need to call the Bubblesort routine from another routine and pass an argument to it (in this case, the data to be sorted).

HTH