Macro to Retrieve Data with Start Year and End Year

[boldcode]

Hi,

I have a worksheet that has row titles in ROW 2 starting from “A2” through “AR2”. My data has thousands of records and always begins in ROW 3. For the purposes of this macro the only columns that matter are "O" through "AR". Columns “O” through “AR” have Years (e.g. 2002, 2003, 2004, etc…) as row titles. The Year row titles start from 2002 (Column “O”) to 2031 (Column “AR”).

I want to create a macro with Two Input Boxes that allow me to select all the data between a Start Year and an End Year. So for example if my Start Year is 2005 and my End Year is 2010, the macro would only give me back the Years 2005, 2006, 2007, 2008, 2009 and 2010 along with the associated data that pertains to those Years.

Additionally, I would want the macro to delete the Years from 2002 through 2004 along with its associated data and also Years 2011 through 2031 along with its associated data. Once these are deleted the Years 2005 through 2010 will shift left and start at column “O” through column “T”.

As part of this post I provide a visual example of what I just described above in the attached workbook "Visual".

[AlphaFrog]

Sub Data_Start_End_Years()
    
    Dim rngStart As Range, rngEnd As Range
    Dim lStart As Long, lEnd As Long
    
    Do
        lStart = Application.InputBox("Enter the start year. ", "Start Year", Type:=1)
        If lStart = 0 Then Exit Sub 'User canceled
        lEnd = Application.InputBox("Enter the end year. ", "End Year", Type:=1)
        If lEnd = 0 Then Exit Sub   'User canceled
        If lEnd < lStart Then MsgBox "The End year must be greater than the Start year. ", vbExclamation, "Invalid Year Entries"
    Loop While lEnd < lStart
    
    Set rngStart = Range("O2:AR2").Find(lStart, , , xlWhole, , xlNext)
    Set rngEnd = Range("O2:AR2").Find(lEnd, , , xlWhole, , xlNext)
    
    If rngStart Is Nothing Then
        MsgBox "Cannot find the start year column. ", vbExclamation, "Start Year Not Found"
        Exit Sub
    ElseIf rngEnd Is Nothing Then
        MsgBox "Cannot find the end year column. ", vbExclamation, "End Year Not Found"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    If rngEnd.Column < 44 Then Columns(rngEnd.Column + 1).Resize(, 44 - rngEnd.Column + 1).Delete
    If rngStart.Column > 15 Then Columns(15).Resize(, rngStart.Column - 15).Delete
    Application.ScreenUpdating = True
    
End Sub

[boldcode]

Thank you AlphaFrog, it works great!