XL2000: Show/Hide Columns Problem

[locutis]

Hello,

I have the following sub which works fine with consecutive columns:

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], .[A9].End(xlToRight))
If rng.Value = r.Value Then
rng.EntireColumn.Hidden = False
Exit For
End If
Next
End With
Next
End With
End Sub

Problem is that there are formula columns in between each dynamically generated column. When the sub hits the first non-dynamically generated column (not mapped to the cell reference on the lookup sheet) the routine stops and I get one column.

Can someone offer some advice on a solution? Is there a way to handle this in code so the columns skip the non-mapped columns or can the columns be mapped directly in the sub?

Thanks in advance,
Jean Luc

 

[GlennUK]

When the sub hits the first non-dynamically generated column (not mapped to the cell reference on the lookup sheet) the routine stops and I get one column.

What lookup sheet? What do you mean by a column not being mapped to a cell reference in that sheet? What do you mean when you say the routine stops : crashes, or just follows the logic you programmed? And what do you mean "I get one column"? Get one column hidden? Get one column showing?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[stevexff]

Problem is that there are formula columns in between each dynamically generated column

Does this mean that in your inner loop you need For i = 0 to blah step 2 rather than a For Each?

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 Steve,

Yes, something like that I believe. Can you show me an example of how I could integrate that logic with the example I provided? I tried it and it wouldn't work with the way I've already set it up.

Thanks,
Jean Luc

 

[SkipVought]

Hi,

How about not exiting the FOR?

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], .[A9].End(xlToRight))
            If rng.Value = r.Value Then
              rng.EntireColumn.Hidden = False
            Else
              rng.EntireColumn.Hidden = True
            End If
          Next
        End With
      Next
    End With
End Sub

Skip,

_[red][/red]