Hi Isabelle,

Am Wed, 14 Jan 2015 12:19:05 -0500 schrieb isabelle:

lastCol = Cells(rng.Row, rng.Columns.Count).End(xlToLeft).Column
^^^^^^
you get a wrong result. Change the line above to:
lastCol = Cells(rng.Row, Columns.Count).End(xlToLeft).Column

or try:

Function myCnt(myRng As Range) As Long
Dim varTmp As Variant
Dim i As Long, LCol As Long

LCol = Cells(myRng.Row, Columns.Count).End(xlToLeft).Column
varTmp = myRng
For i = LCol To 1 Step -1
If Len(varTmp(1, i)) = 0 Then
myCnt = LCol - i
Exit Function
ElseIf i = 1 Then
myCnt = myRng.Cells.Count
End If
Next
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional