Thread: columns in excel to reflect array of values entered in previous two columns

Thanks TMshucks..Yes it helped a lot but the first year is repeated twice ..For example..If A1 has 1995 B1 has 1997 C1 returns 1995,1995,1996,1997...means the first year is repeated..

Also I want to know what to do if the year is entered in two digits? I have 1998 in A1 and 2001 in B1 ..For this requirement what could be the UDF..Can you please clarify as i have an excell file with 17000+ records and years are entered in two digits..

With Regards
Netvasi

Originally Posted by TMShucks

A UDF can do this. For example:

Option Explicit

Function CollateYears(fYear As Range, lYear As Range) As String

Dim lfYear As Long: lfYear = fYear.Value
Dim llYear As Long: llYear = lYear.Value

CollateYears = lfYear
If lfYear = llYear Then Exit Function
Do Until lfYear > llYear
    CollateYears = CollateYears & "," & lfYear
    lfYear = lfYear + 1
Loop
End Function

Copy and paste the code into a standard module and then use it as a normal function.

=CollateYears(A1,B1) gives 2001,2002,2003,2004,2005,2006,2007,2008,2009,2010 if A1 has 2001 and B1 has 2010.

Regards