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