Solved

VBA Code Revision (Involving Hiding of Columns)

Posted on 2015-01-26
10
60 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 30

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 51

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 30

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 33

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 51

Expert Comment

by:Rgonzo1971
ID: 40579183
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
autofill formulas using macro 8 57
Excel (XLSM) on iPad 3 39
Permutacion of 2 numbers COUNT 8 21
Problem to macro 5 24
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

710 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