Compile Error - Procedure too large

D

DAVEYB

I am new to coding and am getting the Compile error message - procedure too
large.

Can anyone advise me how to consense the following code based on a combi box
allowing the user to select a month ?
It is 57 pages long but an extract appears below.

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Columns("O:X").EntireColumn.Hidden = True
Columns("AP:AX").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AZ:BA").EntireColumn.Hidden = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

Many thanks
Dave
 
D

Dave Peterson

You can combine ranges and not select cells:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

And as long as the formulas are consistent, you can do even more:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15:ab18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"


I am new to coding and am getting the Compile error message - procedure too
large.

Can anyone advise me how to consense the following code based on a combi box
allowing the user to select a month ?
It is 57 pages long but an extract appears below.

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Columns("O:X").EntireColumn.Hidden = True
Columns("AP:AX").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AZ:BA").EntireColumn.Hidden = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

Many thanks
Dave
Click to expand...
 
D

Don Guillett

Try this idea
Private Sub ComboBox1_Change()
Columns("A:IV").Hidden = False
If Application.Trim(ComboBox1) = "JANUARY" Then
'MsgBox "hi"
Range("O1:X1,ap1:ax1,z1:aa1,az1:ba1").EntireColumn.Hidden = True
For i = 15 To 18
Range("AB" & i).FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Next i
End If
End Sub
 
D

DAVEYB

Stunning answer - many thanks Dave

Dave Peterson said:
You can combine ranges and not select cells:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

And as long as the formulas are consistent, you can do even more:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15:ab18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"


I am new to coding and am getting the Compile error message - procedure too
large.

Can anyone advise me how to consense the following code based on a combi box
allowing the user to select a month ?
It is 57 pages long but an extract appears below.

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Columns("O:X").EntireColumn.Hidden = True
Columns("AP:AX").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AZ:BA").EntireColumn.Hidden = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

Many thanks
Dave
Click to expand...
Click to expand...
 
R

Rick Rothstein

Columns("A:IV").EntireColumn.Hidden = False

You can save a little bit more by removing the .EntireColumn property
call...

Columns("A:IV").Hidden = False

Because the reference it to Columns, it will do the same thing.

--
Rick (MVP - Excel)


Dave Peterson said:
You can combine ranges and not select cells:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

And as long as the formulas are consistent, you can do even more:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15:ab18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"


I am new to coding and am getting the Compile error message - procedure
too
large.

Can anyone advise me how to consense the following code based on a combi
box
allowing the user to select a month ?
It is 57 pages long but an extract appears below.

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Columns("O:X").EntireColumn.Hidden = True
Columns("AP:AX").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AZ:BA").EntireColumn.Hidden = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

Many thanks
Dave
Click to expand...
Click to expand...
 
D

Don Guillett

Rick, Congrats on your deserved MVP.

BTW, Didn't I say this in my OP?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Rick Rothstein said:
You can save a little bit more by removing the .EntireColumn property
call...

Columns("A:IV").Hidden = False

Because the reference it to Columns, it will do the same thing.

--
Rick (MVP - Excel)


Dave Peterson said:
You can combine ranges and not select cells:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

And as long as the formulas are consistent, you can do even more:

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Range("O:X,AP:AX,Z:AA,AZ:BA").EntireColumn.Hidden = True
Range("AB15:ab18").FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"


I am new to coding and am getting the Compile error message - procedure
too
large.

Can anyone advise me how to consense the following code based on a combi
box
allowing the user to select a month ?
It is 57 pages long but an extract appears below.

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Columns("O:X").EntireColumn.Hidden = True
Columns("AP:AX").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AZ:BA").EntireColumn.Hidden = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

Many thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

BTW, Didn't I say this in my OP?

Yes, but I responded to Dave's posting before I read your response (I
scanned his code, saw the simplification and posted my comment about it
right then and there).
Rick, Congrats on your deserved MVP.
Click to expand...

Thank you very much for your extremely kind comment; I really appreciate it.
However, you might want to go to the microsoft.private.mvp.excel newsgroup
and read the Nate Oliver sub-thread in the main thread with Subject line
"New Excel pages on MSDN" dated 8/26/2008 for the (strange) story behind my
being able to use this signature.
 
D

DAVEYB

Wow !


Don Guillett said:
Try this idea
Private Sub ComboBox1_Change()
Columns("A:IV").Hidden = False
If Application.Trim(ComboBox1) = "JANUARY" Then
'MsgBox "hi"
Range("O1:X1,ap1:ax1,z1:aa1,az1:ba1").EntireColumn.Hidden = True
For i = 15 To 18
Range("AB" & i).FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Next i
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
DAVEYB said:
I am new to coding and am getting the Compile error message - procedure
too
large.

Can anyone advise me how to consense the following code based on a combi
box
allowing the user to select a month ?
It is 57 pages long but an extract appears below.

Private Sub ComboBox1_Change()
Columns("A:IV").EntireColumn.Hidden = False
If ComboBox1 = "JANUARY" Then
Columns("O:X").EntireColumn.Hidden = True
Columns("AP:AX").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AZ:BA").EntireColumn.Hidden = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB17").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"
Range("AB18").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-23]:RC[-14])"

Many thanks
Dave
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fixing a run-time error'9': Subscript out of range 0
Macro not returning correct data 4
Check if name exist in a list 1
need help with macro for 400 worksheets! 2
Application-defined or object -defined error 1
Object Variable or With Block Variable Not Set 3
Two Q's: Trouble Closing Excel & run-Time 462 5
SUMPRODUCT 3

Top