Get Column Letter From Column Number

To get the Letter of a column, there are two ways.

#1 (Simplest way, I think):

Function GetColumnLetter(ColumnNumber As Long) As String
    GetColumnLetter = Split(Columns(ColumnNumber).Address(), “$”)(2)
End Function

#2:

Function GetColumnLetter(ByVal ColumnNumber As Long, _
  Optional ColumnName As String = “”) As String
    If ColumnNumber = 0 Then
        GetColumnLetter = ColumnName
    Else
        ColumnName = Chr(65 + (ColumnNumber – 1) Mod 26) & ColumnName
        ColumnNumber = (ColumnNumber – 1) \ 26
        GetColumnLetter = GetColumnLetter(ColumnNumber, ColumnName)
    End If
End Function

Advertisements

Find The Active Last Column Number

The below code should work in all versions of Excel (if not, please let me know):

Function GetColumnCount(WorksheetName As String, RowNumber As Long) As Long
    With Worksheets(WorksheetName)
        GetColumnCount = .Cells(RowNumber, .Columns.Count).End(xlToLeft).Column
    End With
End Function

Find The Active Last Row Number

The below code should work in all versions of Excel (if not, please let me know):

Function GetRowCount(WorksheetName As String, ColumnNumber As Long) As Long
    With Worksheets(WorksheetName)
        GetRowCount = .Cells(.Rows.Count, ColumnNumber).End(xlUp).Row
    End With
End Function

Sorting Worksheets In Ascending Or Descending Order

Today, I would like to show a code which I created to sort the worksheets in ascending or descending order in the current workbook. I have opted bubble sort to sort the names.

What did I do?

First, I counted the number of worksheets available in the current workbook. Then created an array and saved the worksheets’ names in the array, sorted the names in ascending or descending order (two codes – based on your requirement, you can choose any one) and moved the worksheets according to the sorted order. In the end, show/select the first worksheet.

Here goes the code:

Sub SortWorksheetNamesAscending()
    Dim NoOfSheets As Long, Counter As Long
    Dim Counter1 As Long, Counter2 As Long
    Dim TemporaryVariable As String
    Dim WkShtsNames() As String

    'Count the number of sheets in the workbook
    NoOfSheets = ActiveWorkbook.Worksheets.Count

    'Redefine and assign the names of the worksheets in WkShtsNames array
    ReDim WkShtsNames(1 To NoOfSheets)
    For Counter = 1 To NoOfSheets
        WkShtsNames(Counter) = ActiveWorkbook.Worksheets(Counter).Name
    Next Counter

    'Sort the worksheets' names in ascending order
    For Counter1 = LBound(WkShtsNames) To UBound(WkShtsNames)
        For Counter2 = LBound(WkShtsNames) To UBound(WkShtsNames)
            If WkShtsNames(Counter1) < WkShtsNames(Counter2) Then
                TemporaryVariable = WkShtsNames(Counter1)
                WkShtsNames(Counter1) = WkShtsNames(Counter2)
                WkShtsNames(Counter2) = TemporaryVariable
            End If
        Next Counter2
    Next Counter1

    'Move the worksheets according to the sorted order
    For Counter = 1 To NoOfSheets
        If Counter = 1 Then
            If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then
                ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _
                    ActiveWorkbook.Worksheets(1)
            End If
        ElseIf Counter = NoOfSheets Then
            If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then
                ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _
                    ActiveWorkbook.Worksheets.Count
            End If
        Else
            ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _
                After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets(Counter – 1).Name)
        End If
    Next Counter

    'Select the first worksheet
    ActiveWorkbook.Worksheets(1).Select
End Sub

Sub SortWorksheetNamesDescending()
    Dim NoOfSheets As Long, Counter As Long
    Dim Counter1 As Long, Counter2 As Long
    Dim TemporaryVariable As String
    Dim WkShtsNames() As String

    ‘Count the number of sheets in the workbook
    NoOfSheets = ActiveWorkbook.Worksheets.Count

    ‘Redefine and assign the names of the worksheets in WkShtsNames array
    ReDim WkShtsNames(1 To NoOfSheets)
    For Counter = 1 To NoOfSheets
        WkShtsNames(Counter) = ActiveWorkbook.Worksheets(Counter).Name
    Next Counter

    ‘Sort the worksheets’ names in descending order
    For Counter1 = LBound(WkShtsNames) To UBound(WkShtsNames)
        For Counter2 = LBound(WkShtsNames) To UBound(WkShtsNames)
            If WkShtsNames(Counter1) > WkShtsNames(Counter2) Then
                TemporaryVariable = WkShtsNames(Counter1)
                WkShtsNames(Counter1) = WkShtsNames(Counter2)
                WkShtsNames(Counter2) = TemporaryVariable
            End If
        Next Counter2
    Next Counter1

    ‘Move the worksheets according to the sorted order
    For Counter = 1 To NoOfSheets
        If Counter = 1 Then
            If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then
                ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _
                    ActiveWorkbook.Worksheets(1)
            End If
        ElseIf Counter = NoOfSheets Then
            If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then
                ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _
                    ActiveWorkbook.Worksheets.Count
            End If
        Else
            ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _
                After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets(Counter – 1).Name)
        End If
    Next Counter

    ‘Select the first worksheet
    ActiveWorkbook.Worksheets(1).Select
End Sub