I'm using the following code to number each cell in column A if there is a
value in column B. I need help setting my variable 1 when it start.

Sub count1()
Dim i As Integer
i = i + 1
Do Until Range("B" & i) = ""
i = i + 1
Range("a" & i) = i

Loop

End Sub

Thanks for any help

Hi Ram,

Does this do what you want?


Sub count1()
Dim i As Integer
i = 1
Do Until Range("B" & i) = ""
Range("a" & i) = i
i = i + 1
Loop

End Sub

Ken Johnson

Hi Ram,

Or is this what you're trying to do?....

Sub count1()
Dim iLastRow As Long
Dim I As Long
Dim J As Long
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 = _
WorksheetFunction.CountA(Range("B:B")) - J + 1
End If
Next I

End Sub

Ken Johnson

Thanks Ken this works great

"ram" wrote:

I'm using the following code to number each cell in column A if there is a
value in column B. I need help setting my variable 1 when it start.

Sub count1()
Dim i As Integer
i = i + 1
Do Until Range("B" & i) = ""
i = i + 1
Range("a" & i) = i

Loop

End Sub

Thanks for any help

Thanks Ken this works great

"Ken Johnson" wrote:

Hi Ram,

Or is this what you're trying to do?....

Sub count1()
Dim iLastRow As Long
Dim I As Long
Dim J As Long
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 = _
WorksheetFunction.CountA(Range("B:B")) - J + 1
End If
Next I

End Sub

Ken Johnson

Thanks Ken this works great

"Ken Johnson" wrote:


Hi Ram,

Does this do what you want?


Sub count1()
Dim i As Integer
i = 1
Do Until Range("B" & i) = ""
Range("a" & i) = i
i = i + 1
Loop

End Sub

Ken Johnson

ram wrote:
Thanks Ken this works great

You're welcome ram. Thanks for the feedback.

Ken Johnson

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