Solved

VBA to hide hide specific columns on more than one worksheet

Posted on 2014-03-05
10
166 Views
Last Modified: 2014-03-22
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
0
Comment
Question by:Andreamary
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
0
 

Author Comment

by:Andreamary
Comment Utility
Still receiving the error, and I've checked the spreadsheet names as recommended...
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Could you send a dummy example

EDIT

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

Regards
0
 
LVL 8

Expert Comment

by:5teveo
Comment Utility
Try

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

Author Comment

by:Andreamary
Comment Utility
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

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

Regards
0
 

Author Comment

by:Andreamary
Comment Utility
No, column G is empty...
0
 
LVL 8

Expert Comment

by:5teveo
Comment Utility
If you want 1 line command...

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

Author Comment

by:Andreamary
Comment Utility
I will create a dummy spreadsheet and upload it...

Andrea
0
 

Author Closing Comment

by:Andreamary
Comment Utility
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!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now