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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
> > message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote
in
> > > > message
> > > > > > > news:[email protected]...
> > > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >