Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

VBA Code Revision (Involving Hiding of Columns)

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
Geekamo
Asked:
Geekamo
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
Wilder1626Commented:
Yes

Just do this:

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

End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

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

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

Regards
0
 
Wilder1626Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gowflowCommented:
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
 
Rgonzo1971Commented:
Shorter:

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

Open in new window

Regards
0
 
gowflowCommented:
Thums up Rgonzo !!!!
didn't know that one, we never cease to learn :)

gowflow
0
 
Wilder1626Commented:
wow, this is very good :)
0
 
Rob HensonIT & Database AssistantCommented:
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
 
GeekamoAuthor Commented:
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now