VBA to hide hide specific columns on more than one worksheet

Andreamary
Andreamary used Ask the Experts™
on
I am currently using this code below to hide columns H and M on the Cartoworkflow sheet. I would also like to hide column G on a worksheet called "NEW_Procedures" located in the same workbook. I tried to just add a line in the code below, but am getting an error message.

Private Sub Workbook_Open()
    Sheets("Cartoworkflow").Columns("h:h").ColumnWidth = 0
    Sheets("Cartoworkflow").Columns("m:m").ColumnWidth = 0
    If WorksheetFunction.CountA(Sheet8.Cells) = 0 Then
        InitCopyRow
    End If
End Sub

Please advise...thanks!
Andrea
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try

    Sheets("Cartoworkflow").Range("h:h,m:m").ColumnWidth = 0
    Sheets("NEW_Procedures").Range("g:g").ColumnWidth = 0

Open in new window


if you get an error pls verify the Sheets names for blank spaces or other typing errrors.

Regards

Author

Commented:
Still receiving the error, and I've checked the spreadsheet names as recommended...
Top Expert 2016

Commented:
Could you send a dummy example

EDIT

What is the text of the error and where does the code stop?

Regards
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Top Expert 2012

Commented:
Try

Sheets("Cartoworkflow").Select
Columns("G:G").Select
Selection.EntireColumn.Hidden = True

Author

Commented:
Rgonzo, since you answered first I'll respond to your questions before trying other solutions offered..

I get a run-time error 1004
Unable to set the ColumnWidth property of the Range class

When I click on debug the following line is highlighed in yellow:

Sheets("NEW_Procedures").Range("g:g").ColumnWidth = 0

Due to the confidentiality and complexity of the workbook I haven't had the time to create a dummy yet. I'm hoping to find someone that can offer a solution without going that route, but understand that may not be possible, in which case I'll follow up...

Andrea
Top Expert 2016

Commented:
Hi,

Do you have some buttons or checkboxes on the G column?

Regards

Author

Commented:
No, column G is empty...
Top Expert 2012

Commented:
If you want 1 line command...

Sheets("NEW_Procedures").Columns("G:G").Hidden = True

Author

Commented:
I will create a dummy spreadsheet and upload it...

Andrea

Author

Commented:
Sorry to have taken so long to close this question. The spreadsheet was undergoing some substantive structural changes and I've been waiting on input. Upon completion of the changes, I tried this solution and it worked perfectly. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial