How to: Hide a column on multipal sheets without looping.

[Kent Prokopy]

I want to be able to hide a column on all sheets of a workbook without having
to loop through each sheet. I have tried the following but it only hides the
column on sheet1

Sub Macro1()
' Macro1 Macro
' Macro recorded 8/20/2008 by Kent Prokopy
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Columns("D").EntireColumn.Hidden = True
End Sub

It makes no sense that I have to use a loop for this.

 

[sbitaxi]

I want to be able to hide a column on all sheets of a workbook without having
to loop through each sheet. I have tried the following but it only hides the
column on sheet1

Sub Macro1()
' Macro1 Macro
' Macro recorded 8/20/2008 by Kent Prokopy
  Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
  Columns("D").EntireColumn.Hidden = True
End Sub

It makes no sense that I have to use a loop for this.

Kent,

To my knowledge, VBA does not support range functions on multiple
sheet selections. You can change properties for multiple sheets at a
time using ActiveWorkbook.Sheets

You will have to loop through your worksheets to hide the column.

S

 

[Kent Prokopy]

I was afrade of this answer.

Thank you.

Kent,

To my knowledge, VBA does not support range functions on multiple
sheet selections. You can change properties for multiple sheets at a
time using ActiveWorkbook.Sheets

You will have to loop through your worksheets to hide the column.

S

 

[Jim Thomlinson]

Here is the code. Really no longer than yours. It has the added benefit of
not crashing if you change the sheet names or add / remove sheets...

Sub HideStuff()
dim wks as worksheet

for each wks in worksheets
wks.range("D").Entirecolumn.hidden = true
next wks
end sub

 

[Kent Prokopy]

Thank you for your time, But I was hoping to be able to do this without
looping through sheets.

 

[Rick Rothstein \(MVP - VB\)]

Why are you so set against looping through the sheets? Are you concerned
about having to select the different sheets? Look at Jim's code carefully...
notice that you do not have to select a sheet in order to do something on it
(you will remain on the same sheet you were on when you executed the code).

Rick

 

[Kent Prokopy]

My concern is speed. Looping is slow.

Why are you so set against looping through the sheets? Are you concerned
about having to select the different sheets? Look at Jim's code carefully...
notice that you do not have to select a sheet in order to do something on it
(you will remain on the same sheet you were on when you executed the code).

Rick

 

[Rick Rothstein \(MVP - VB\)]

I'm afraid VBA requires you to loop the sheets... like 'sbitaxi', I know of
no way to go from sheet to sheet without using looping.

Rick