Developers Depot

Sub Main()

Sort Range(”A2:C5″), “C”, xlAscending

End Sub

Sub Sort(DataRangeWithoutHeader As Range, ColumnLetterToSort As String, SortOrder As XlSortOrder)

Dim OriginalSelectedRange As Range
Dim keyRange As Range
Dim Column As Variant
Dim ColumnVisibility As New Collection

‘Remeber what was selected
Set OriginalSelectedRange = Selection

‘prevent user from watching the screen update
Application.ScreenUpdating = False

‘unhide any hidden cols and add to Collection Variable
For Each Column In DataRangeWithoutHeader.Columns
If Column.EntireColumn.Hidden = True Then
Column.EntireColumn.Hidden = False
ColumnVisibility.Add Column
End If
Next Column

‘clear
ActiveSheet.Sort.SortFields.Clear

’sort
Set keyRange = Range(ColumnLetterToSort & DataRangeWithoutHeader.Row & “:” & ColumnLetterToSort & DataRangeWithoutHeader.End(xlDown).Row)
ActiveSheet.Sort.SortFields.Add key:=keyRange, SortOn:=xlSortOnValues, order:=SortOrder, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange DataRangeWithoutHeader
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

‘put back any hidden columns
Do While ColumnVisibility.Count > 0
Set Column = ColumnVisibility.Item(1)
Column.EntireColumn.Hidden = True
ColumnVisibility.Remove (1)
Loop

‘Select the original range
OriginalSelectedRange.Select

‘Restore Default
Application.ScreenUpdating = True

End Sub

Sub Main()
       Dim ColumnNumber As Integer
       ColumnNumber = 1
       ‘Range(”A1″)=”Hello World”
    range(ColumnLetter(ColumnNumber) & 1) = “Hello World”
   End Sub

Function ColumnLetter(ColumnNumber As Integer) As String
   If ColumnNumber > 26 Then

    ‘ 1st character:  Subtract 1 to map the characters to 0-25,
    ‘                 but you don’t have to remap back to 1-26
    ‘                 after the ‘Int’ operation since columns
    ‘                 1-26 have no prefix letter

    ‘ 2nd character:  Subtract 1 to map the characters to 0-25,
    ‘                 but then must remap back to 1-26 after
    ‘                 the ‘Mod’ operation by adding 1 back in
    ‘                 (included in the ‘65′)

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ‘ Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
 End Function