PDA

View Full Version : (Un)Hide columns based on a list



theta
02-14-2012, 12:19 PM
Hi all...

I have a named range in Worksheets("INDEX").Range("EMAILCOLS") that contains details of columns to be hidden before I email the sheet

The sheet containing the data ("POSITIONS") has a range defined called 'filteredrange'

I wanted to do a function to check each cell in row 1, and if Find is not nothing for "EMAILCOLS" then hide the entire column

Then reverse it after the email send. I cannot get it to work though :(

Would like it to be simple e.g.

r1 = Worksheets("POSITIONS").Range("filteredrange")
r2 = Worksgeets("INDEX").Range("EMAILCOLS")

ColumnVisible(r1,r2,0) <--- Use 0 = False to hide the columns
ColumnVisible(r1,r2,0) <--- Use 1 = True to show the columns

For the logic I was considering

For each rng in r2 - check if Find gives a result in r1.Rows(1) and if it does then entirecolumn.hidden = true but I can't get it right

Any help appreciated

Kind regards

Bob Phillips
02-14-2012, 12:36 PM
Set r1 = Worksheets("POSITIONS").Range("filteredrange")
Set r2 = Worksgeets("INDEX").Range("EMAILCOLS")

For Each rng In r2

rng.EntireColumn.Hidden = Not IsError(Application.Match(rng.Value, r1, 0))
Next rng

theta
02-15-2012, 03:14 AM
How would I achieve this using Find - it runs very slow with match

Bob Phillips
02-15-2012, 05:07 AM
Have you turned off Screenupdating and set calculation mode to manual?