Hi ram,

After a little more thought I now realise my code is a little
defective. Nothing serious, it's just that as it stands it is a lot
slower than it can be. You might not notice the slow speed, the number
of rows in column B with values might not be all that large. However,
if the number of rows is substantial (I tried it out with around 65000)
the code takes about 300 seconds to run. The reason for the slow speed
is the count of non-blank cells in column B
(WorksheetFunction.CountA(Range("B:B"))) is inside the loop. I should
not have placed that inside the loop. Its value is constant and it only
needs to be evaluated the once.
The following code fixes up this problem. This amended code only takes
around 3 seconds to run, 100 times faster!...

Sub count1()
Dim iLastRow As Long
Dim I As Long
Dim J As Long
Dim iNonBlankCount As Long
iNonBlankCount = WorksheetFunction.CountA(Range("B:B"))
iLastRow = Range("B" & Range("B:B").Rows.Count).End(xlUp).Row
For I = iLastRow To 1 Step -1
If Range("B" & I).Value < "" Then
J = J + 1
Range("A" & I).Value = _
iNonBlankCount - J + 1
End If
Next I
End Sub


Ken Johnson