Conditionally showing hidden columns in XL 2000 1

[locutis]

Hi all,

I have a sheet with many hidden columns. What I wanted to know, is if I could conditionally show these hidden columns if their column header cell is populated with a value. In other words, on a separate data sheet are my values. Then, using one of those values, I'm populating a value for the column header(s). So, all the other cells underneath that column header cell would appear as one whole column, but only if that column header cell has a value.

Here is a sample of my source table, which is populated with MS Query:

Test Program 1 8/5/2006 123452.01
Test Program 1 8/6/2006 123452.02
Test Program 1 8/8/2006 123452.03
Test Program 1 8/7/2006 123452.04

The last column is the value I'm using to populate the Column Header on the display table:

520467.01 520467.02 520467.03 520467.03
Empty Cell Empty Cell Empty Cell Empty Cell

So, if I have 123452.05, then that hidden column would now become visible. I have up to 99 hidden columns, because I know it will never go beyond that number according to our business rules.

Hope that makes sense. Thanks,
Jean Luc
















Hope that makes sense. Thanks.
Jean Luc

 

[SkipVought]

Hi,

Use your macro recorder to FIND one of the values in row 1.

Post back with your code. We will loop thru the values returnd from your query and then do a FIND for each of these values and assign TRUE to the Visible property of the EntireColumn.

Skip,

_[red][/red]

 

[locutis]

Hi, well, here's what the macro recorder picked up after I ran it while entering a value which populated the first column header:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/13/2006 by Test R
'

'
Range("B2").Select
ActiveCell.FormulaR1C1 = "520467"
Range("B3").Select
End Sub

Not sure this is what I need though.

 

[SkipVought]

"Use your macro recorder to FIND one of the values in row 1."

Skip,

_[red][/red]

 

[stevexff]

Wrote some similar code to this a while back. Mine was slightly more complex, as I wanted to pop up a dialog with tabs and checkboxes generated dynamically from values in the data, but a scaled-down version would work.

I created a named range containing the partial row that contained all the cells that I wanted to use for selection criteria, in this case your column headings.

Then it is just a case of iterating through the cells in the named range, comparing the values, and setting .EntireColumnHidden to either True or False accordingly.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[locutis]

Hi Skip,

Sorry, I'm not sure what you mean. Do you mean just to go to one of the cells in ro1 and click on its value? Can you please explain in more detail what I actually need to do? Sorry I'm not understanding you, because I'm not exactly sure what you mean.

Thanks,
Jean Luc

 

[SkipVought]

Edit/Find

Skip,

_[red][/red]

 

[locutis]

Hi Skip,

OK, here it is. I recorded an Edit/Find:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 11/14/2006 by jean Luc
'

'
Cells.Find(What:="520467.01", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub

Thanks,
Jean Luc

 

[locutis]

Steve, I've been trying to do what you suggest. Could you please show me a code sample to get started with?

 

[SkipVought]

Sub HideColumns()
    Dim r As Range, rng As Range
    With Sheets(1)
        For Each r In .Range(.[A2], .[A2].End(xlDown))
            Set rng = Sheets(2).Cells.Find(r.Value)
            If Not rng Is Nothing Then
                rng.EntireColumn.Hidden = False
            End If
        Next
    End With
End Sub

Skip,

_[red][/red]

 

[locutis]

Thanks Skip. When I run this sub nothing happens, but I'll keep trying. At least now I have something to work with and learn from.

 

[locutis]

Hi Skip,

I set-up test data on Sheet1(A1:J2), with the columns referencing the values on Sheet2 i.e. (=Sheet2!A2):
1 2 3 4 5 6

and on Sheet2(A1:A10):
1
2
3
4
5
6
7

I know you think I'm a total lamer, but could you please tell me where I'm going wrong? I understand what's supposed to happen here but for some reason it's not hiding columns if I remove one of the values on Sheet2.

But nothing happens, just an endless loop. Here's your code I modified to test with:

Sub HideColumns()
Dim r As Range, rng As Range
With Sheets(1)
For Each r In .Range(.[A2], .[A9].End(xlDown))
Set rng = Sheets(2).Cells.Find(r.Value)
If Not rng Is Nothing Then
rng.EntireColumn.Hidden = False
End If
Next
End With
End Sub

Thanks,
Jean Luc

 

[SkipVought]

1. Pay attention to the sheet index. 1 is the first sheet, 2 is the second sheet (counting from the left) Substitute sheet names if you wish.

2. so Sheet(1) has the LIST starting in A2 and continuing in a contiguous list.

3. Sheet(2) has the headings in a row

Skip,

_[red][/red]

 

[locutis]

Hi Skip,

I've been trying this for hours, no luck. I understand what you're telling me, but it doesn't work:

Sheet1(A2:A10):

1234.01
1234.02
1234.03
1234.04
1234.05
1234.06
1234.07

Sheet 2:
Nothing happens.

Thanks,
Jean Luc
1234.08
1234.09

 

[SkipVought]

are not ANY of these values in sheet(2) also?

It works for me.

What data is on the FIRST tab in the workbook?

What data is on the SECOND tab in the workbook?

Skip,

_[red][/red]

 

[locutis]

Hi Skip,

Data on the first tab(Sheet1.A2:A10):
1234.01
1234.02
1234.03
1234.04
1234.05
1234.06
1234.07
1234.08
1234.09


Data on the second tab(Sheet2.A2:H2):
1234.01 1234.02 1234.03 1234.04 1234.05 1234.06 1234.07 1234.08

Then I'm hiding, unhiding, testing, etc. But can't get any results. I'm sure I'm missing something.

Thanks for being patient, I'm getting there.

Jean Luc

 

[SkipVought]

"...conditionally show these hidden columns if their column header cell is populated with a value"


Are your columns HIDDEN to begin with?

Skip,

_[red][/red]

 

[locutis]

Skip, one more thing. It looks like it's looping through, the sheet2 flashes and I have to hit escape to stop it, (code execution has been interrupted).
Jean Luc

 

[locutis]

Yes, I'm hiding some of the cloumns on sheet2.

 

[locutis]

OK, I hide the columns on sheet2(A2:H2) and these cells are referencing the values on sheet1. It goes into an endless loop, but after I break execution I'm left with B column showing the value from =Sheet1!A3.