|
Compile Error - Procedure too large
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 |
Compile Error - Procedure too large
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 mo 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])" DAVEYB wrote: 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 -- Dave Peterson |
Compile Error - Procedure too large
Stunning answer - many thanks Dave
"Dave Peterson" wrote: 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 mo 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])" DAVEYB wrote: 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 -- Dave Peterson |
Compile Error - Procedure too large
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" wrote in message ... 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 mo 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])" DAVEYB wrote: 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 -- Dave Peterson |
Compile Error - Procedure too large
|
Compile Error - Procedure too large
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. 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. -- Rick (MVP - Excel) |
Compile Error - Procedure too large
Wow !
"Don Guillett" wrote: 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 "DAVEYB" wrote in message ... 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 |
| All times are GMT +1. The time now is 11:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com