VBA Excel trying to hide the same columns across multiple sheets

Question

I have exactly the same columns to hide across several workbooks.

Unfortunately, the following code doesn't work well:

Dim sh As Worksheet
Dim sNames As Variant
sNames = Array("BoM North", "BoM North Extras", "BoM South")
Dim BomColumns As Range
Set BomColumns = Columns("I:J")
For Each sh In sNames
  Sheets(sh).BomColumns.Hidden = True
Next was

As I a getting an error: Object required on the following line:

For each sh in Shnames

The code was picked up from here:

https://www.mrexcel.com/board/threads/vba-to-hide-columns-on-multiple-sheets.961675/

What object should be assigned here?

UPDATE:

With the following code:

Dim ws As Worksheet 'Dim wsq As Worksheet

Dim BomColumns As Range Set BomColumns = Columns("I:J")

For Each ws In Sheets(Array("BoM North", "BoM North Extras", "BoM South"))
With ws.Range(BomColumns)
.Hidden = True
End With
Next was

I have an error: Method 'Range of Worksheets' failed at the following line: With ws.Range(BomColumns)

Try replacing Sheets(sh).BomColumns.Hidden = True with Sheets(sh).range(BomColumns.address).EntireColumn.Hidden = True. Set BomColumns = Columns("I:J") sets a range in the active sheet. So you cannot use it in other sheets. But its address, you can... You can do it without preliminarily setting a range: Sheets(sh).Columns("I:J").Hidden = True... — FaneDuru
– FaneDuru, Commented Feb 17, 2023 at 10:59
still the same. Should Set BoMColumns = Columns("I:J") go on different place? — Geographos
– Geographos, Commented Feb 17, 2023 at 11:01
You can set it inside the iteration, but it is useless, anyhow. Try the way I suggested after editing: Sheets(sh).Columns("I:J").Hidden = True. — FaneDuru
– FaneDuru, Commented Feb 17, 2023 at 11:02
This is different... sh must be declared as Variant or as String. It is not a worksheet... Your code iterates between strings of the array. I did not observe initially where the error is raised. — FaneDuru
– FaneDuru, Commented Feb 17, 2023 at 11:08

Razputine · Accepted Answer · 2023-02-17 12:23:07Z

2

This is a minimalistic way of doing it:

Dim ws As Worksheet

  For Each ws In ThisWorkbook.Worksheets
      ws.Columns("I:J").Hidden = True
    Next

If it is not all sheets you need, simply replace ThisWorkbook.Worksheets with the range needed

answered Feb 17, 2023 at 12:23

Razputine

614 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel trying to hide the same columns across multiple sheets

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related