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