store variable

[keen learner]

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

 

[Bob Phillips]

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]

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]

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)

 

[keen learner]

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]

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]

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]

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]

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]

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)