Select a column based on a cell entry
 

I've been struggling for a day now in trying to write a macro that will
cycle through all the worksheets in a workbook, then check each column
for the word "HIDE" in a certain row (15) in each column, then if it
finds it, hides the column.

e.g.(with a bit of psudo code thrown in :-(

For I = 1 To Worksheets.Count
ActiveWorkbook.Worksheets(I).Select

For Each col In Worksheets(I).Columns

if row 15 = "HIDE"
Then
Column(col).Select
Selection.EntireColumn.Hidden = True
Next col

Next


I've tried most things, but my VBA skills are not yet good enough to
manage this.

Any help anybody?? Please??

Select a column based on a cell entry
 

You were very, very close to having it!

Public Sub HideColumns()
Dim I as Integer, col As Range

For I = 1 To ThisWorkbook.Worksheets.Count
For Each col In Worksheets(I).Columns
If col.Cells(15, 1) = "HIDE" Then col.Hidden = True
Next col
Next I

End Sub

NOTE: in code you do not need to Select a book, sheet, or range in order to
use it, as long as you provide the proper object references
--
- K Dales


" wrote:

I've been struggling for a day now in trying to write a macro that will
cycle through all the worksheets in a workbook, then check each column
for the word "HIDE" in a certain row (15) in each column, then if it
finds it, hides the column.

e.g.(with a bit of psudo code thrown in :-(

For I = 1 To Worksheets.Count
ActiveWorkbook.Worksheets(I).Select

For Each col In Worksheets(I).Columns

if row 15 = "HIDE"
Then
Column(col).Select
Selection.EntireColumn.Hidden = True
Next col

Next


I've tried most things, but my VBA skills are not yet good enough to
manage this.

Any help anybody?? Please??

Select a column based on a cell entry
 

Many thanks
Works a treat -
Ah - "very, very close to having it", you are too kind.
Unfortunately not close enough for Excel :-(
It's a bit like "sort of" -
"This is your pilot speaking, we will be landing in London in 30 minutes -
sort of" :-)

Many thanks again -
Regards Alan F.


"K Dales" wrote in message
...
You were very, very close to having it!

Public Sub HideColumns()
Dim I as Integer, col As Range

For I = 1 To ThisWorkbook.Worksheets.Count
For Each col In Worksheets(I).Columns
If col.Cells(15, 1) = "HIDE" Then col.Hidden = True
Next col
Next I

End Sub

NOTE: in code you do not need to Select a book, sheet, or range in order
to
use it, as long as you provide the proper object references
--
- K Dales


" wrote:

I've been struggling for a day now in trying to write a macro that will
cycle through all the worksheets in a workbook, then check each column
for the word "HIDE" in a certain row (15) in each column, then if it
finds it, hides the column.

e.g.(with a bit of psudo code thrown in :-(

For I = 1 To Worksheets.Count
ActiveWorkbook.Worksheets(I).Select

For Each col In Worksheets(I).Columns

if row 15 = "HIDE"
Then
Column(col).Select
Selection.EntireColumn.Hidden = True
Next col

Next


I've tried most things, but my VBA skills are not yet good enough to
manage this.

Any help anybody?? Please??