Home

Consulting

Excel VBA

Legal

Links

Contact

About Us

Find Last Column With Specified Row

'********************************************************************
'  http://www.KravatzInc.com    1-866-XLS-PROG
'
'  This code is copyrighted by Kravatz, Inc. (c) 2007 All Rights Reserved.
'  You may use this code as long as this message appears
'  There is no warranty made to the fitness or suitablilty of this code
'  and by using it you hold Kravatz, Inc. harmless for any damages
'  that may result from its use.  This code is supplied as is.
'  
'********************************************************************
Function FindLastCol(r As Long) As Long
'This routine will find the last row in the spread sheet for the passed column
'Hidden column aren't included
' c is the column to use to find the last row
Dim prevcol As Long

'Go to the end
If r <= 0 Then
    FindLastCol = 0
    Exit Function
    
End If

Cells(r, Cells.Columns.Count).Select

  Selection.End(xlToLeft).Select
prevcol = 0
Do While Trim(ActiveCell.Text) = "" And ActiveCell.Column <> 1
      Selection.End(xlToLeft).Select
      
      If ActiveCell.Column = prevcol Then
        FindLastCol = ActiveCell.Column
        Exit Function
      Else
        prevcol = ActiveCell.Column
        
      End If
    
Loop
If ActiveCell.Text = "" Then
    FindLastCol = 0
Else
    FindLastCol = ActiveCell.Column
End If

End Function