Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
Assistance Please
jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
Sub HideCols()
Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
im iLastCol As Long
Dim iStartCol As Long Hi Bob, I have a problem. The macro returns the error code Runtime error, application or or object defined error. My Data starts on a6 hence i added 'Range ("a6") select Range("A6").Select iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub "Bob Phillips" wrote: Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
The selecting is not necessary, just state your start point in the code, I
had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
Hi Bob
Thanks for the effort but nothing is happening, the macro is not hiding any colums. What I need for the macro to do is the following 1) Go to row 6 (where my data starts0 2)from row 6 search and identify by column the last column that contains data(as every month i add a new column.) 3)Offset the last column(iLastCol) by 6 4)Hide columns from column b to iLastCol-6 (Column A contains the Row headers) Thanks in anticipation "Bob Phillips" wrote: The selecting is not necessary, just state your start point in the code, I had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
This is a bit more robuts, but what data do you have in row 6?
Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long Dim iNumCols As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = 2 If iLastCol - 12 2 Then iStartCol = iLastCol - 12 iNumCols = iLastCol - iStartCol + 1 - 6 If iNumCols 0 Then With Columns(iStartCol).Resize(, iNumCols) .Hidden = Not .Hidden End With End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob Thanks for the effort but nothing is happening, the macro is not hiding any colums. What I need for the macro to do is the following 1) Go to row 6 (where my data starts0 2)from row 6 search and identify by column the last column that contains data(as every month i add a new column.) 3)Offset the last column(iLastCol) by 6 4)Hide columns from column b to iLastCol-6 (Column A contains the Row headers) Thanks in anticipation "Bob Phillips" wrote: The selecting is not necessary, just state your start point in the code, I had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
Hi Bob,
I thought you have abandoned me. Here is a sample of the data Column A Row 6 to row 8 contains the Header Rows and the column headers start in B5 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Warranty 345 875 1,405 1,935 2,465 Goodwill 4,567 5,678 6,789 7,900 9,011 Warr & Gdwl 4,912 6,553 8,194 9,835 Your macro is still not yielding any results so far, I have tried the following macro which i have created myself Sub HideUnhideadf() ' ' HideUnhideadf Macro ' Macro recorded 2006/03/08 by Private ' Dim mycolumn As Long ' Range("A6").Select Selection.End(xlToRight).Select ActiveCell.Offset(0, -6).Select mycolumn = ActiveCell.Column Columns("B:mycolumn").Select Selection.EntireColumn.Hidden = True End Sub This returns the error code "RUNTIME ERROR 13 Type Mismatch" i AM TRYING TO HIDE CLOUMNS B TO Mycolumn "Bob Phillips" wrote: This is a bit more robuts, but what data do you have in row 6? Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long Dim iNumCols As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = 2 If iLastCol - 12 2 Then iStartCol = iLastCol - 12 iNumCols = iLastCol - iStartCol + 1 - 6 If iNumCols 0 Then With Columns(iStartCol).Resize(, iNumCols) .Hidden = Not .Hidden End With End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob Thanks for the effort but nothing is happening, the macro is not hiding any colums. What I need for the macro to do is the following 1) Go to row 6 (where my data starts0 2)from row 6 search and identify by column the last column that contains data(as every month i add a new column.) 3)Offset the last column(iLastCol) by 6 4)Hide columns from column b to iLastCol-6 (Column A contains the Row headers) Thanks in anticipation "Bob Phillips" wrote: The selecting is not necessary, just state your start point in the code, I had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
The reason for that is because I interpreted your request as wanting to show
the latest 6 months. As there are less than 6 here I don't hide anything. What do you want to happen in this case, and also when you have say 9 months? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob, I thought you have abandoned me. Here is a sample of the data Column A Row 6 to row 8 contains the Header Rows and the column headers start in B5 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Warranty 345 875 1,405 1,935 2,465 Goodwill 4,567 5,678 6,789 7,900 9,011 Warr & Gdwl 4,912 6,553 8,194 9,835 Your macro is still not yielding any results so far, I have tried the following macro which i have created myself Sub HideUnhideadf() ' ' HideUnhideadf Macro ' Macro recorded 2006/03/08 by Private ' Dim mycolumn As Long ' Range("A6").Select Selection.End(xlToRight).Select ActiveCell.Offset(0, -6).Select mycolumn = ActiveCell.Column Columns("B:mycolumn").Select Selection.EntireColumn.Hidden = True End Sub This returns the error code "RUNTIME ERROR 13 Type Mismatch" i AM TRYING TO HIDE CLOUMNS B TO Mycolumn "Bob Phillips" wrote: This is a bit more robuts, but what data do you have in row 6? Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long Dim iNumCols As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = 2 If iLastCol - 12 2 Then iStartCol = iLastCol - 12 iNumCols = iLastCol - iStartCol + 1 - 6 If iNumCols 0 Then With Columns(iStartCol).Resize(, iNumCols) .Hidden = Not .Hidden End With End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob Thanks for the effort but nothing is happening, the macro is not hiding any colums. What I need for the macro to do is the following 1) Go to row 6 (where my data starts0 2)from row 6 search and identify by column the last column that contains data(as every month i add a new column.) 3)Offset the last column(iLastCol) by 6 4)Hide columns from column b to iLastCol-6 (Column A contains the Row headers) Thanks in anticipation "Bob Phillips" wrote: The selecting is not necessary, just state your start point in the code, I had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
Hi Bob
You assumed right I gave you a sample of the data only. on my database I have data containing over 36 months 's data, . and I would want the macro to alternate between displaying the latest 6 or 12 months data. I applied your macro to a copy of the real data spanning over 36 months and it had no effect. If the worksheet contains less than 6 months data then the macro needs to do nothing (Unlikely scenario) Perhaps the focus should be set to active worksheet??? "Bob Phillips" wrote: The reason for that is because I interpreted your request as wanting to show the latest 6 months. As there are less than 6 here I don't hide anything. What do you want to happen in this case, and also when you have say 9 months? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob, I thought you have abandoned me. Here is a sample of the data Column A Row 6 to row 8 contains the Header Rows and the column headers start in B5 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Warranty 345 875 1,405 1,935 2,465 Goodwill 4,567 5,678 6,789 7,900 9,011 Warr & Gdwl 4,912 6,553 8,194 9,835 Your macro is still not yielding any results so far, I have tried the following macro which i have created myself Sub HideUnhideadf() ' ' HideUnhideadf Macro ' Macro recorded 2006/03/08 by Private ' Dim mycolumn As Long ' Range("A6").Select Selection.End(xlToRight).Select ActiveCell.Offset(0, -6).Select mycolumn = ActiveCell.Column Columns("B:mycolumn").Select Selection.EntireColumn.Hidden = True End Sub This returns the error code "RUNTIME ERROR 13 Type Mismatch" i AM TRYING TO HIDE CLOUMNS B TO Mycolumn "Bob Phillips" wrote: This is a bit more robuts, but what data do you have in row 6? Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long Dim iNumCols As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = 2 If iLastCol - 12 2 Then iStartCol = iLastCol - 12 iNumCols = iLastCol - iStartCol + 1 - 6 If iNumCols 0 Then With Columns(iStartCol).Resize(, iNumCols) .Hidden = Not .Hidden End With End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob Thanks for the effort but nothing is happening, the macro is not hiding any colums. What I need for the macro to do is the following 1) Go to row 6 (where my data starts0 2)from row 6 search and identify by column the last column that contains data(as every month i add a new column.) 3)Offset the last column(iLastCol) by 6 4)Hide columns from column b to iLastCol-6 (Column A contains the Row headers) Thanks in anticipation "Bob Phillips" wrote: The selecting is not necessary, just state your start point in the code, I had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
store variable
I just tested it for 13 months and it hides columns fine. You mention
activesheet, are you running the macro on the activesheet? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob You assumed right I gave you a sample of the data only. on my database I have data containing over 36 months 's data, . and I would want the macro to alternate between displaying the latest 6 or 12 months data. I applied your macro to a copy of the real data spanning over 36 months and it had no effect. If the worksheet contains less than 6 months data then the macro needs to do nothing (Unlikely scenario) Perhaps the focus should be set to active worksheet??? "Bob Phillips" wrote: The reason for that is because I interpreted your request as wanting to show the latest 6 months. As there are less than 6 here I don't hide anything. What do you want to happen in this case, and also when you have say 9 months? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob, I thought you have abandoned me. Here is a sample of the data Column A Row 6 to row 8 contains the Header Rows and the column headers start in B5 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Warranty 345 875 1,405 1,935 2,465 Goodwill 4,567 5,678 6,789 7,900 9,011 Warr & Gdwl 4,912 6,553 8,194 9,835 Your macro is still not yielding any results so far, I have tried the following macro which i have created myself Sub HideUnhideadf() ' ' HideUnhideadf Macro ' Macro recorded 2006/03/08 by Private ' Dim mycolumn As Long ' Range("A6").Select Selection.End(xlToRight).Select ActiveCell.Offset(0, -6).Select mycolumn = ActiveCell.Column Columns("B:mycolumn").Select Selection.EntireColumn.Hidden = True End Sub This returns the error code "RUNTIME ERROR 13 Type Mismatch" i AM TRYING TO HIDE CLOUMNS B TO Mycolumn "Bob Phillips" wrote: This is a bit more robuts, but what data do you have in row 6? Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long Dim iNumCols As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = 2 If iLastCol - 12 2 Then iStartCol = iLastCol - 12 iNumCols = iLastCol - iStartCol + 1 - 6 If iNumCols 0 Then With Columns(iStartCol).Resize(, iNumCols) .Hidden = Not .Hidden End With End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Hi Bob Thanks for the effort but nothing is happening, the macro is not hiding any colums. What I need for the macro to do is the following 1) Go to row 6 (where my data starts0 2)from row 6 search and identify by column the last column that contains data(as every month i add a new column.) 3)Offset the last column(iLastCol) by 6 4)Hide columns from column b to iLastCol-6 (Column A contains the Row headers) Thanks in anticipation "Bob Phillips" wrote: The selecting is not necessary, just state your start point in the code, I had use A1. Try Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(6, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Sub HideCols() Dim iLastCol As Long Dim iStartCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iStartCol = iLastCol - 12 With Columns(iStartCol).Resize(, 6) .Hidden = Not .Hidden End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keen learner" wrote in message ... Assistance Please jan feb mar apr may june july aug sep oct nov dec 12 mth Bakkies 5 9 56 45 34 13 87 90 54 34 34 54 519 cars 7 8 23 34 76 9 19 23 35 56 38 34 467 trucks 23 56 67 45 76 32 54 23 56 23 54 54 762 I have a excell spreadsheet setup as above and every month i add a new colum I would like to write a macro that can enable me to display 6 month data and 12 months data alternately or in other words hide and unhide unnecssary colums Please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to store current row into variable | Excel Programming | |||
How to store text to variable | Excel Programming | |||
Store Variable with Exported Worksheet | Excel Programming | |||
Store the Spreadsheets filename in a variable? | Excel Programming | |||
Store variable value for Chart | Excel Programming |