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
Solved

VBA Code Revision (Involving Hiding of Columns)

Posted on 2015-01-26
10
58 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 50

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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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 50

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 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 50

Expert Comment

by:Rgonzo1971
ID: 40579183
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Calculation 4 48
VBA to Delete the Filtered Rows in an Excel Table 3 32
MS Excel Multi Sheet Formula 13 32
Excel formula to report date modified 14 24
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 …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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