How to select range for EntireColumn.Hidden = True (or False)?

[miche7]

Hi there - -

The project is to track merchandise sales from selected booths, and to have the macro show/hide certain sheets/rows/columns based upon which booths will be selling merchandise. The problem is on TheBank sheet with showing/hiding a range of columns which vary depending upon entries on the Index sheet.

The Excel workbook has:
Index – one sheet which contains the criteria for customizing the workbook
Boothxx - one sheet for each booth with individual mdse items with inventory/sales tracking – show/hide whole sheet depending upon criteria on Index sheet (code works)
Summary – summary of all sales/mdse for all the booths, with one booth to a row with only booths in use being visible (code works)
TheBank - keeps track of cash/sales – one booth uses 9 columns – (code doesn’t work) Problem: How to show/hide and unhide columns for the selected booth – defining a variable range

The Index worksheet has:
Row -ColA – Offset(,1) – Offset(,2)
10 Y Booth01 Bank01
11 -- Booth02 Bank02
12 Y Booth03 Bank03
etc… to Booth08

The merchandiser will enter a Y (or y) in Col-A cell of the booth(s) being used. Any or all booths may be used. Clicking a "Customize Workbook" button performs the show/hide. The macro also needs the ability to re-customize the workbook, i.e., if Booth02 was not needed when the Customize button was first clicked, then afterward becomes necessary to use (=Y), then all the Booth02 sheets/rows/columns need to be visible as well as those Booths that were there before. There also seems to be some conflicts when range is already hidden and I’m trying to hide it again.

Thanks so much for any guidance. Code follows…

Miche7

Sub Customize()    ‘ Customize Button on Index sheet
‘
Dim r As Integer  ' Row counter
Dim stSheetxx As String  ' Worksheet Name for individual Booth
Dim Loc As String    ' Booth named range for 9 columns on TheBank worksheet
' ?  Should Loc be defined as a Range?
    
' Initialize Row for Visible/Hidden Booth sheets
r = 0
Worksheets("TheBank").Unprotect ‘ Prepare TheBank for customizing
Worksheets("Summary").Unprotect   ‘ Prepare Summary for customizing

For r = 0 To 7
        Worksheets("Index").Activate   ‘ Obtain criteria
        stSheetxx = Range("A10").Offset(r, 1).Value  ‘ set booth worksheet name
‘ Set Booth range name as defined on TheBank (series of 9 columns)
        Loc = Range("A10").Offset(r, 1).Value 

‘ Is Booth being used for event?
            If Range("A10").Offset(r).Value = "Y" Or Range("A10").Offset(r).Value = "y" Then
                Worksheets(stSheetxx).Visible = True  ' Show the individual booth worksheet
                Worksheets("Summary").Activate   ' Make sure the Booth-row is visible on Summary
                    Rows(6 + r).EntireRow.Hidden = False  ' One row per booth
                Worksheets("TheBank").Activate
'  Make sure the columns for banking are visible for this booth
'  Need code to unhide columns if hidden
'  Having problems defining a variable range name
'  Every attempt has generated errors
                    Range(Loc).Select
                    Selection.EntireColumn.Hidden = False

‘ If Usage cell on Index is not Y or y 
‘         then hide all sheets/rows/columns for that booth
                Else: Worksheets(stSheetxx).Visible = False   ' Hide individual booth worksheet
                Worksheets("Summary").Activate
                    Rows(6 + r).EntireRow.Hidden = True  ‘ Hide Booth summary row
                Worksheets("TheBank").Activate
‘ Booth columns start in N, 9 columns per booth
‘ Range names (Bank01, Bank02, etc.) on TheBank have been defined
‘ Need code with variable range to hide the 9 columns for selected booth
‘ Tried this both with and without quotes around Loc – generated errors
                 If Range(Loc).Hidden = False Then
                        Range(Loc).Hidden = True
                  End If
             End If
    Next r

Worksheets("TheBank").Protect
Worksheets("Summary").Protect

End Sub

 

[TonyJollans]

Hi miche7,

There's no point in re-inventing the wheel. I haven't gone through your code properly but the way to show/hide rows based on this sort of criteria is to use an AutoFilter (Data > Filter > AutoFilter). If you want code to see how to use it, just record yourself doing it manually.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[