Filling in years

**bfloppy** · 09-09-2011, 07:00 PM

Not sure if its the forum, but here I go.

Okay, i have some data like
rb 1999-2003
rs 1984-1988

and I want to fill in the years like
rb 1999
rb 2000
rb 2001
rb 2002
rb 2003
rs 1984
rs 1985
rs 1986
rs 1987
rs 1988

any ideas?

**Armando Montes** · 09-09-2011, 07:17 PM

Try

Sub SplitYears()
FY = Left(Range("A2"), 4)
LY = Right(Range("A2"), 4)
NY = LY - FY + 2
Range("A2") = FY
Range("A2").AutoFill Range("A2:A" & NY), xlFillSeries
End Sub

**Marcol** · 09-09-2011, 09:38 PM

@ Armando
Perhaps you should first, check your idea, and then maybe expand it a little.
With data in Column A, Row 2 Down and the result in Column B

Option Explicit

Sub SplitYears()
    Dim LastRow As Long, NextRow As Long, RowNo As Long
    Dim FY As Long, LY As Long, n As Long
    Dim strPrefix As String

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    For RowNo = 2 To LastRow
        strPrefix = Left(Range("A" & RowNo), InStr(1, Range("A" & RowNo), " "))
        FY = Mid(Range("A" & RowNo), InStr(1, Range("A" & RowNo), " ") + 1, 4)
        LY = Right(Range("A" & RowNo), 4)
        n = LY - FY
        NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1
        With Range("B" & NextRow)
            .Value = strPrefix & FY
            .AutoFill Range("B" & NextRow & ":B" & NextRow + n), xlFillSeries
        End With
    Next
End Sub