VBA Snippets

Col_Letter

Converting a column number to its equivalent column letter.

Public Function Col_Letter(ByVal iColNo As Integer) As String
Dim sstartletter As String
   On Error GoTo ErrorHandler
   Select Case iColNo
      Case 0:         Col_Letter = Chr(90)
      Case Is <= 26:  Col_Letter = Chr(iColNo + 64)
      Case Else
         If iColNo Mod 26 = 0 Then
            Col_Letter = Chr(64 + (iColNo / 26) - 1) & Col_Letter(iColNo Mod 26)
            Exit Function
         End If
         sstartletter = Chr(Int(iColNo / 26) + 64)
         Col_Letter = sstartletter & Col_Letter(iColNo Mod 26)
   End Select
   Exit Function
ErrorHandler:
   Call MsgBox(Err.Number & " - " & Err.Description)
End Function

Col_Number

Converting a column letter to its equivalent column number.

Public Function Col_Number(ByVal sColChar As String, _ 
                  Optional ByVal sWshName As String = "") _ 
                           As Integer 
Dim istartnumber As Integer 
   On Error GoTo ErrorHandler 
   If Len(sColChar) = 1 Then 
      If sWshName <> "" Then _ 
         Col_Number = Worksheets(sWshName).Range(sColChar & "1").Column 
      If sWshName = "" Then Col_Number = Range(sColChar & "1").Column 
   Else 
      istartnumber = Range((Left(sColChar, 1)) & "1").Column 
      Col_Number = ((istartnumber)) * 26 * (Len(sColChar) - 1) + _ 
                                      Col_Number(Right(sColChar, Len(sColChar) - 1)) 
   End If 
   Exit Function 
ErrorHandler: 
   Call MsgBox(Err.Number & " - " & Err.Description) 
End Function

Row_LastPopulatedColumn

Public Function Row_LastPopulatedColumn(ByVal lrowno As Long, _
                                        ByVal lstartcolno As Long) As Long
Dim lcolno As Long
   On Error GoTo ErrorHandler
   lcolno = lstartcolno
   Do While Len(Cells(lrowno, lcolno).Value) > 0
      lcolno = lcolno + 1
   Loop
   Row_LastPopulatedColumn = lcolno - 1
   Exit Function
ErrorHandler:
   Call MsgBox(Err.Number & " - " & Err.Description)
End Function