Hide columns based on cell value - Unable to set hidden range property of Range Class

**trillium** · 05-12-2012, 02:27 PM

test.xlsmHi

I'm trying to write a very simple code (not using case select) to unhide columns based on variable entered into cell C5. (It's a number from 1 - 12 to represent a month)

I tried using offset columns based on the month entered (so... if month is March (3)... ) but I'm getting a error message that I can't figure out....

"Unable to set hidden range property of Range Class"... help?!

Sub RunningAverageQualityScore()

Dim iMonth As Integer 'review month
Dim FinalColumn As Integer 'end of chart
Dim FirstColumn As Integer 'beginning of chart
Dim ColumnsHide As Integer 'counts columns to hide


FinalColumn = Range("FinalColumn").Select
FirstColumn = Range("FirstColumn").Select
iMonth = Range("C5").Value
ColumnsHide = -12 + iMonth

Range("FinalColumn").Offset(0, ColumnsHide).Hidden = True



End Sub

**JieJenn** · 05-12-2012, 02:33 PM

perhaps change your last line to

Columns(Range("FinalColumn").Offset(0, ColumnsHide).Column).Hidden = True

**trillium** · 05-12-2012, 02:45 PM

hmmm... no longer get an error message but now nothing happens at all.... hmmmm....

**JieJenn** · 05-12-2012, 04:27 PM

Easier if you post your workbook with description

**trillium** · 05-12-2012, 06:59 PM

Here it is! :-)

**JieJenn** · 05-12-2012, 07:06 PM

Not exactly sure if this is what you are looking for since you didn't provide me with description of the issue, but try

PS: Move your month value in D4 to C3.

Cells.Columns.Hidden = False
Columns(Range("C3") + 3).Hidden = True

**trillium** · 05-12-2012, 07:24 PM

hmmm just unhides all columns...???

**trillium** · 05-13-2012, 11:13 PM

GOT IT!!!

Sub unhide()
'hides columns based on value in cell C3 (month)

Dim iMonth As Long
Dim iStart As Long
Dim iStop As Long

iMonth = Range("C3").Value
iStart = (4 + iMonth)
iStop = 15
 
 'ensures all columns unhidden to start
    Columns("C:P").Select
    Selection.EntireColumn.Hidden = False

'once we get to 12, all columns are now unhidden, so don't need code to hide
If iMonth = 12 Then
    Columns("C:P").Select
    Selection.EntireColumn.Hidden = False
Else

'if <12, then hides unneeded columns
    Range(Columns(iStart), Columns(iStop)).Select
    Selection.EntireColumn.Hidden = True
    
End If

Range("C3").Select

End Sub

Hide columns based on cell value - Unable to set hidden range property of Range Class

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Re: Hide columns based on cell value - Unable to set hidden range property of Range Class

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions