Fill Series of Sequential Numbers in Column A Based on Last Value in Column B - VBA

[CC268]

This should be fairly straight forward, but I am having a tough time getting it figured out. I simply want to insert a blank Column A and then fill in Column A with a series of sequential numbers all the way down until the last value in Column B.

Here is what I have so far...

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub FillSeries()

    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    'r = row, c = column
    Application.Goto Reference:="R1C2" 
    With Worksheets("Sheet1")
    LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
    .Range("B1:B" & LastRow).Select

    End With</code>

Cell B2 will always be blank so we need to make sure we are using the .End(xlUp).Row function to make sure the code isn't stopping at cell B2 and is capturing the entire range of Column B.

Unfortunately I can't use file sharing sites here at work, so I am unable to upload a sample. However, here is a photo to give you an idea of what I want done.

 

[MickG]

Perhaps:-

[COLOR="Navy"]Sub[/COLOR] MG24Jan13
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]With[/COLOR] Range("A1")
    .Value = "1"
    .AutoFill Destination:=Range("A1").Resize(Lst), Type:=xlFillSeries
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[CC268]

Wow beautiful! So simple too...worked perfect. Thanks a ton!

 

[MickG]

You're welcome

 

[Rick Rothstein]

Since you are starting at Row 1, you could also use this macro...

Sub SeriesFillColumnA()
  With Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = Evaluate("ROW(" & .Address & ")")
  End With
End Sub

 

[zookeepertx]

Hi MickG

A year and a half later and this is exactly the solution I needed!

Thanks!

 

[MickG]

You're welcome

 

[KrystynaD]

Since you are starting at Row 1, you could also use this macro...

Sub SeriesFillColumnA()
  With Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = Evaluate("ROW(" & .Address & ")")
  End With
End Sub

Since you are starting at Row 1, you could also use this macro...

Sub SeriesFillColumnA()
  With Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = Evaluate("ROW(" & .Address & ")")
  End With
End Sub

Since you are starting at Row 1, you could also use this macro...

Sub SeriesFillColumnA()
  With Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = Evaluate("ROW(" & .Address & ")")
  End With
End Sub

How different would the macros be if i was starting in row 2 (A2)?
I have tried changing the range from A1:A to A2:A - but the sequential numbers start at 2 instead of 1 in A2.

 

[Rick Rothstein]

I am not sure why you quoted my code three times but here is the code for starting at Row 2...

Sub SeriesFillColumnA()
  With Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = Evaluate("ROW(" & .Address & ")-1")
  End With
End Sub

Note that what is being subtracted is one less than the start row number. If you started at Row 5, you would subtract 4. The reason there was no subtraction in my original code is when you start at Row 1, one less than that is zero and since subtracting zero does not change a value, it was omitted.

 

[KrystynaD]

Thank you! Your explanation makes so much sense!