Dear all,

I have a sheet that compares this year's sales to last year and to our
sales budget.

What I am trying to do is to hide the columns that relate to either
last year's sales or forecast, and came up with the following code. I
know almost nothing about VBA, so I contructed this by recording macros
for each stage and then editing them together. I have attached it to a
button on my worksheet.

This is my code:

Private Sub CommandButton1_Click()
' This shows the Last Year Comparison
'
'
'
Range("A:GP").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
Range("D:D,G:G,I:I,L:L,N:N,Q:Q,S:S,V:V,Y:Y,AB:AB,
AD:AD,AG:AG,AI:AI,AL:AL,AN:AN,AQ:AQ,AS:AS,AV:AV,
AY:AY,BB:BB,BD:BD,BG:BG,BI:BI,BL:BL,BN:BN,BQ:BQ,BS :BS,
BV:BV,BY:BY,CB:CB,CD:CD,CG:CG,CI:CI,CL:CL,CN:CN,CQ :CQ,
CS:CS,CV:CV,CY:CY,DB:DB,DD:DD,DG:DG,DI:DI,DL:DL,DN :DN,
DQ:DQ,DS:DS,DV:DV,DY:DY,EB:EB,ED:ED,EG:EG,EI:EI,EL :EL,
EN:EN,EQ:EQ,ES:ES,EV:EV,EY:EY,FB:FB").Select (All this is in 1 row on
my macro)
Range("D1").Activate
Selection.EntireColumn.Hidden = True
End Sub

When I run it, it comes up with the error:

Runtime Error '1004':

Method 'Range' of object '_worksheet' failed.

When I click Debug it highlights the long range.

Does anybody know what I'm doing wrong?

Many thanks

Chris


--
Tibbs
------------------------------------------------------------------------
Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
View this thread: http://www.excelforum.com/showthread...hreadid=517770

why doesn't your macro not work?

--
davesexce
-----------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread.php?threadid=51777

I noticed that and edited out the double negative...

Chris


--
Tibbs
------------------------------------------------------------------------
Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
View this thread: http://www.excelforum.com/showthread...hreadid=517770

Try it in pieces.

Private Sub CommandButton1_Click()
' This shows the Last Year Comparison
'
Dim r(1 To 11) As Range
Range("A:GP").EntireColumn.Hidden = False
Set r(1) = Range("D:D,G:G,I:I,L:L,N:N,Q:Q,S:S")
Set r(2) = Range("V:V,Y:Y,AB:AB,AD:AD,AG:AG")
Set r(3) = Range("AI:AI,AL:AL,AN:AN,AQ:AQ,AS:AS,AV:AV")
Set r(4) = Range("AY:AY,BB:BB,BD:BD,BG:BG,BI:BI,BL:BL")
Set r(5) = Range("BN:BN,BQ:BQ,BS:BS,BV:BV,BY:BY,CB:CB")
Set r(6) = Range("CD:CD,CG:CG,CI:CI")
Set r(7) = Range("CL:CL,CN:CN,CQ:CQ,CS:CS,CV:CV,CY:CY")
Set r(8) = Range("DB:DB,DD:DD,DG:DG,DI:DI,DL:DL,DN:DN")
Set r(9) = Range("DQ:DQ,DS:DS,DV:DV")
Set r(10) = Range("DY:DY,EB:EB,ED:ED,EG:EG,EI:EI,EL:EL")
Set r(11) = Range("EN:EN,EQ:EQ,ES:ES,EV:EV,EY:EY,FB:FB")
For i = 1 To 11
r(i).EntireColumn.Hidden = True
Next

End Sub

--
Regards,
Tom Ogilvy

"Tibbs" wrote in
message ...

I noticed that and edited out the double negative...

Chris


--
Tibbs
------------------------------------------------------------------------
Tibbs's Profile:
http://www.excelforum.com/member.php...o&userid=15947
View this thread: http://www.excelforum.com/showthread...hreadid=517770

Hi there I can it to work up to AS on my macro recorder
Range( _

"D:D,G:G,I:I,L:L,N:N,Q:Q,S:S,V:V,Y:Y,AB:AB,AD:AD,A G:AG,AI:AI,AL:AL,AN:AN,AQ:AQ,AS:AS
_
).Select
Selection.EntireColumn.Hidden = True
Range("A1").Select

I recorded this from the macro recorder, don't have time to do all th
columns there may be a glitch after that, maybe record in again bu
only hide 10 columns at a time so you can have more contro

--
davesexce
-----------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread.php?threadid=51777

Tom,

That worked - many thanks & much appreciated.

Chris


--
Tibbs
------------------------------------------------------------------------
Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
View this thread: http://www.excelforum.com/showthread...hreadid=517770