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

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

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

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

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

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

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

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

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

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