Autofill - Autonumbering

[Greg]

I am trying to place line numbering down one column of my
data. I have tried cheating and using autofill, but when
I enter a variable it doens't seem to work. Here is what
I have got right now;

For h = 15 To Cells(Rows.Count, "B").End(xlUp).Row
If Range("B" & h).Value <> "" Then
Range("A15:A16").Select
Selection.AutoFill Destination:=Range("A15"
& ":A" & h), Type:=xlFillDefault
End If
Next h

The autofill is cheating and I would like to know if
their is a better way of doing it.

Thanks in advance,
Greg

 

[Tom Ogilvy]

Here is one approach:
Sub Tester2()
Dim rng As Range
Set rng = Cells(Rows.Count, "B").End(xlUp)
Range("A15").Value = 1
Range("A15").AutoFill Destination:= _
Range(Range("A15"), rng.Offset(0, -1)), _
Type:=xlFillSeries
End Sub

if you are trying to number just in cells where column B has values.

Sub Tester3()
Dim rng As Range
Dim i As Long, cell As Range
Set rng = Cells(Rows.Count, "B").End(xlUp)
Set rng = Range(Range("B15"), rng)
i = 1
For Each cell In rng
If cell <> "" Then
cell.Offset(0, -1).Value = i
i = i + 1
End If
Next
End Sub

 

[John McGimpsey]

Autofill is hardly cheating. One way:

With Range("B" & Rows.Count).End(xlUp)
If .Row > 16 Then
Range("A15:A16").AutoFill _
Destination:=Range("A15:A" & .Row), _
Type:=xlFillDefault
End If
End With

 

[Tom Ogilvy]

Doesn't seem to do anything if A15 and A16 are blank.

this worked:
Sub AAA1()
With Range("B" & Rows.Count).End(xlUp)
If .Row > 16 Then
Range("A15:A16") = Evaluate("{1;2}")
Range("A15:A16").AutoFill _
Destination:=Range("A15:A" & .Row), _
Type:=xlFillDefault
End If
End With
End Sub

Were you assuming they were filled or does it work the way written on the
MAC?

 

[J.E. McGimpsey]

I was assuming (from the OP's original code) that they were filled.