Hiding columns outside a user specified range

[Fatrobo]

Morning all,

I have a resourcing spreadsheet in which we want to be able to use buttons to display only the colums relating to the weeks of particular jobs. I currently have created some code which hides all columns except for the current quarter. I now need to make the project specific buttons. So if Project A runs for June and July (user input), I want the code to hide all of the other columns.

I didn't imagine this would be such technical code. All I wanted was to hide columns outside a users specified range. I'm not sure my code is even on the right path though. The code I have so far has mostly been put together from reserching forums such as this one. The code I am using is below but seem to be getting an error "method 'Range' of object '_worksheet' failed".

[COLOR=#333333]Sub Hide_ProjectA()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">'
' Hide_ProjectA Columns
' Hides columns outside a specified project date range

'Unhide all columns
Columns("C:BC").Hidden = False  'Unhide all week Columns


'If Target.Address = "$B$6:$B$7" Then

Dim the_selection_Before As String
Dim the_selection_After As String
Dim Week_review As String

the_selection_Before = Sheet1.Range("$B$6")

    Dim Rep As Integer
    For Rep = 3 To 54
        the_column = GetColumnLetter_ByInteger(Rep)
        Week_review = Sheet1.Range(the_column & "15")
         If Week_review < the_selection_Before Then
         Sheet1.Range(the_column & ":" & the_column).EntireColumn.Hidden = True
         Else
         Sheet1.Range(the_column & ":" & the_column).EntireColumn.Hidden = False
    End If
    Next Rep

the_selection_After = Sheet1.Range("$B$7")

Dim Rep2 As Integer
For Rep2 = 3 To 54
    the_column = GetColumnLetter_ByInteger(Rep)
    Week_review = Sheet1.Range(the_column & "4")
    If Week_review > the_selection_After Then
    Sheet1.Range(the_column & ":" & the_column).EntireColumn.Hidden = True
    Else
End If
Next Rep2

'End If
Application.ScreenUpdating = True </code>[COLOR=#333333]End Sub[/COLOR]

And the GetColumnLetter_ByInteger code:

[COLOR=#333333]Public Function GetColumnLetter_ByInteger(what_number As Integer) As String[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">GetColumnLetter_ByInteger = ""

MyColumn_integer = what_number

    If MyColumn_integer <= 26 Then
    colum_letter = Chr(64 + MyColumn_integer)
    End If
    
    If MyColumn_integer > 26 Then
    column_letter = Chr(Int((MyColumn_integer - 1) / 26) + 64) & Chr(((MyColumn_integer - 1) Mod 26) + 65)
    End If
    
    GetColumnLetter_ByInteger = column_letter
 </code>[COLOR=#333333]End Function[/COLOR]

 

[Fatrobo]

I am using Microsoft Excel 2010 and 64-bit windows 7. I couldn't figure out how to attached an excel file as an example. I have a number of columns which reflect the weeks in a year. Each column is headed by it's starting date (row 15). I have a spot for the user to enter in a start date and end date for the project the user inputs in cell B6 and B7. All columns outside this I need hidden.