Column Range Variable

[mattcottrell]

I am trying to hide certain columns based on a value entered into a cell.

This is what I currently have:


Sub Prepare()
Dim MonthNumber As Integer
Dim MonthRange As Range

MonthNumber = ActiveSheet.Range("C3&quot.Value

If MonthNumber = 1 Then
Set MonthRange = Columns("P:AA&quot
ElseIf MonthNumber = 2 Then
Set MonthRange = Columns("Q:AB&quot
End If


Sheets(Array("100%", "Absence", "Absence Per Head", "Pension Take Up", _
"Labour Turnover", "Staff Turnover", "Headcount", "Starters", "Leavers", "Summary&quot) _
.Select
Sheets("100%&quot.Activate
Columns("C:AL&quot.EntireColumn.Hidden = True
MonthRange.EntireColumn.ColumnWidth = 8.43


End Sub


All the sheets are in exactly the same format.

The code:
"Columns("C:AL&quot.EntireColumn.Hidden = True" works fine, but
"MonthRange.EntireColumn.ColumnWidth = 8.43" does not work.

Does anybody know how to successfully pass a column variable to VBA.

Many thanks in advance
Matt

 

[RobBroekhuis]

The property assignment by itself should work just fine (you can leave the "entirecolumn" bit out, actually, but it works either way). What doesn't work? Do you get an error?
Rob

 

[mattcottrell]

It hides columns C to AL ok, but it doesn't set the column width of the specified columns to 8.43 (does nothing, no error)

It does work if I hardcode the columns in instead of using the variable. eg: "Columns("P:AA&quot.ColumnWidth = 8.43"

 

[RobBroekhuis]

In that case, why not use a string variable:

Dim strMonthRange As string
...
If MonthNumber = 1 Then
strMonthRange = "P:AA"
...
Columns(strMonthRange).ColumnWidth = 8.43

Rob

 

[mattcottrell]

Supurb, You are a true genius. That worked a treat.

Once again many thanks
Matt

 

[RobBroekhuis]

Wow, I've always wanted to be a genius - thanks for granting me my dearest wish ;-)
Rob

 

[xlbo]

LOL LOL LOL LOL LOL LOL LOL LOL

Function trueGenius(Member as string)as Boolean
With Forum.Member
If .PeopleHelped > Lots then
trueGenius = TRUE
else
trueGenius = FALSE
end if
end with
end function

Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[rmikesmith]

Can anyone tell me where I can download the xlbo.xla Add-In containing neat custom functions such as trueGenius??

TIA

Mike

 

[RobBroekhuis]

He'd send it to you, but it's so large, the e-mail would be rejected by your mail server
Rob

 

[xlbo]

It'll be available in June for the bargain knockdown price of £10,000 (well, who can put a price on being able to tell if you have true genius or not ???)

Also includes the new functions:

WhatsMyPayRaise(AssKisser as Boolean, NumScrewups as Boolean, HoursAtWork as Long)

IsItMyFault(BossTemper as Variant)

and the ever popular

ThisWeeksLotteryNumbers(AreYouASucker as Boolean)

Heh Heh Heh Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[lespaul]

Could you also include the following function?

MakeMeAGenius(programmer as variant)


Ok?! Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[xlbo]

Unfortunatley, that function takes years to "calculate" and so can seriously affect the performance of your workbooks (and life ;-) ) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur