Solved

VBA Code Revision (Involving Hiding of Columns)

Posted on 2015-01-26
10
51 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

863 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

21 Experts available now in Live!

Get 1:1 Help Now