Solved

VBA Code Revision (Involving Hiding of Columns)

Posted on 2015-01-26
10
57 Views
Last Modified: 2015-02-01
Hello Experts!

I recorded a macro, that hides a group of columns. While this is only two lines of code, I was wondering - is there an even more stream lined way of saying it?  Is it possible to do the same thing, with only one line of code?

Thank you in advance for your help! :)

Option Explicit

Sub HideColumns()
    Columns("J:AB").Select
    Selection.EntireColumn.Hidden = True
End Sub

Open in new window

0
Comment
Question by:Geekamo
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40571833
Yes

Just do this:

Sub HideColumns()
    
    Columns("J:AB").EntireColumn.Hidden = True

End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40572175
Hi,

Since you already use columns you do not have to specify entirecolumn

Sub HideColumns()
    Columns("J:AB").Hidden = True
End Sub

Regards
0
 
LVL 11

Assisted Solution

by:Wilder1626
Wilder1626 earned 250 total points
ID: 40572509
Another way could be to use the macro to hide or unhidden the columns with the same macro command

Example:
Sub HideColumns()
If Columns("J:AB").Hidden = True Then
  Columns("J:AB").Hidden = False
Else
  Columns("J:AB").Hidden = True
End If
End Sub

Open in new window

0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 
LVL 29

Expert Comment

by:gowflow
ID: 40572520
OP wants one line of code.

Wilder your's would be

Sub HideColumns()
If Columns("J:AB").Hidden = True Then Columns("J:AB").Hidden = False Else Columns("J:AB").Hidden = True
End Sub

Open in new window


gowflow
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40572525
Shorter:

Sub ShowAndHideColumns()
Columns("J:AB").Hidden = Columns("J:AB").Hidden = False
End Sub

Open in new window

Regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40572552
Thums up Rgonzo !!!!
didn't know that one, we never cease to learn :)

gowflow
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40572557
wow, this is very good :)
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40572862
Its even possible without VBA.

Select the columns to be hidden and go to the Data Menu and select Group. This will then add little bullets in the top margin above the columns and a button in the margin above the column to the right of the last column selected (AC in this example). This button will change Automatically for use to Hide or Unhide the columns.

The Grouping settings can be changed such that the button appears to the left of the Group if so required.

This Grouping function is particularly useful if the column before/after those selected is a Summary of the selected columns.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:Geekamo
ID: 40578947
Thank you everyone for your input!

I loved seeing this code get smaller and smaller, and even more advanced (show & hide).

@ Wilder1626 - Your solution worked great! :)

@ Rgonzo1971 - Looking through all of the solutions provided, ultimately I decided to go with your solution. Short code, and better yet - code I am able to read through & understand. :)

How should I award points?

I'm not sure if I should award points to first correct answer?  Or the answer that ultimately I will be using that will work best for my situation.

What is the proper way to award points?

Again, thank you all for your solutions!
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40579183
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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