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]

I copied the data into my workbook, range my code, and it works perfectly.

Are the values EXACTLY the values you posted?

select the range and change the number format to GENERAL.



Skip,

_[red][/red]

 

[locutis]

OK Skip,

(I shifted the cells up one and adjusted accordingly in the sub):

Sheet1.A1:A9:
1234.01
1234.02
1234.03
1234.04
1234.05
1234.06
1234.07
1234.08
1234.09

Sheet2.A1:I1
A1=Sheet1!A1 B1=Sheet1!A2 C1=Sheet1!A3 and so forth through I1.

I hide the columns on Sheet2.A1:I1. All i get is the sheet in an endless loop and I have to break execution.

Here is the sub again just to make sure I haven't messed it up:
Sub ShowColumns()
Dim r As Range, rng As Range
With Sheets(1)
For Each r In .Range(.[A1], .[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]

Did you check the VALUES as I suggested?

 For Each r In .Range(.[A1], .[b][A1][/b].End(xlDown))

Skip,

_[red][/red]

 

[PHV]

And this (type, untested) ?
Sub ShowColumns()
Dim r As Range, rng As Range
With Sheets(1)
For Each r In .Range(.[A1], .[A9].End(xlDown))
With Sheets(2)
For Each rng In .Range(.[A1], .[A1].End(xlRight))
If rng.Value = r.Value Then
rng.EntireColumn.Hidden = False
Exit For
End If
Next
End With
Next
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[locutis]

Hi PH,

When I run your code, I get:
Run-time error '1004'
Application-defined or object-defined error

And this line is highlighted when I debug:
"For Each rng In .Range(.[A1], .[A1].End(xlRight))"

Also, if I mouseover on the 'rng' value in the line below:
"If rng.Value = r.Value Then"

I get:
rng.Value = <Object variable or With block variable not set>

Thanks,
Jean Luc

 

[PHV]

OOps, sorry for the typo:
Sub ShowColumns()
Dim r As Range, rng As Range
With Sheets(1)
For Each r In .Range(.[A1], .[A1].End(xlDown))
With Sheets(2)
For Each rng In .Range(.[A1], .[A1].End([!]xlToRight[/!]))
If rng.Value = r.Value Then
rng.EntireColumn.Hidden = False
Exit For
End If
Next
End With
Next
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Did you check the VALUES as I suggested?

Skip,

_[red][/red]

 

[locutis]

Yes Skip I did that and everything else you suggested. I don't know what else to do. Doesn't the last error I reported imply that there is a syntax error to do with one of the code blocks? Guess not if you have the same code working. In any case, would it be possible for me to get a sheet that you have working as a sample?

 

[locutis]

THANK YOU PHV!!!! IT WORKED, IT WORKED!!!!! THANK YOU TOO SKIP FOR TRYING TO HELP!