combining hide columns with autofilter rows

Hi, I'm new today, so I hope I'm following the rules! (If I've done this incorrectly - sorry - just tell me the right way, please!)

I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.

On the 'Input' sheet I have a button called Format Graphs which controls the following macro:

Private Sub CommandButton1_Click()
Sheets("Projection").Select
    Selection.AutoFilter Field:=1, Criteria1:="<=100", Operator:=xlAnd
    Sheets("Chart Data").Select
    Selection.AutoFilter Field:=1, Criteria1:="<=100", Operator:=xlAnd
    Sheets("Input").Select
End Sub

This limits the maximum age to 100.

On the 'Projection' sheet I have another button called Open All which counteracts the above command and resets the sheet.

In the Excel Programming forum I thought I'd found the answer in RoyUK's
ToggleColumns spreadsheet here
http://www.excelforum.com/excel-prog...de-column.html

My control row is also 3 and I have amended this so that a column with 1 is to be hidden.

However, I'm having difficulty in making the is work - Any ideas will be gratefully received.

The reason for doing this is to produce tidy graphs without orphan legends caused by blank columns.

If I'm allowed to ask another question here - ideally I would like to link the
autofilter to cell Input V42 which is where I control the max age for other functions. How can I do this, please?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


http://www.screencast.com/users/VBAN...a-307763111404

also will be moved to programming as per forum rules

VBA Noob

Sub HideColumns()
    Dim cell As Range

    For Each cell In Intersect(ActiveSheet.UsedRange, Rows(3))
        cell.EntireColumn.Hidden = cell.Value = 1
    Next cell
End Sub

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with Code Tags.

Sub HideColumns()
    Dim cell As Range

    For Each cell In Intersect(ActiveSheet.UsedRange, Rows(3), _
                               Union(Range("B:Z"), Range("AZ:BH")))
        cell.EntireColumn.Hidden = cell.Value = 1
    Next cell
End Sub

If that works, would you please mark the thread as Solved?


Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes