Macro - Columns Are not Hidding / unhiding correctly

G

GarethCW

New Member
Joined
Feb 14, 2024
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hi Could you please assist with the below. macro to hide and unhide various columns. however it does not run correctly, in which columns meant to be hidden are not as well as columns not meant to be hidden are hidden:

VBA Code: 
Sub Macro1()
'
' Macro1 Macro
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = False
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = False
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = False
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = False
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = False
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = False
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = False
    Columns("I:I").Select
    Selection.EntireColumn.Hidden = False
    Columns("J:J").Select
    Selection.EntireColumn.Hidden = False
    Columns("K:K").Select
    Selection.EntireColumn.Hidden = False
    Columns("L:L").Select
    Selection.EntireColumn.Hidden = True
    Columns("M:M").Select
    Selection.EntireColumn.Hidden = True
    Columns("N:N").Select
    Selection.EntireColumn.Hidden = True
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = False
    Columns("P:P").Select
    Selection.EntireColumn.Hidden = True
    Columns("Q:Q").Select
    Selection.EntireColumn.Hidden = True
    Columns("R:R").Select
    Selection.EntireColumn.Hidden = False
    Columns("S:S").Select
    Selection.EntireColumn.Hidden = False
    Columns("T:T").Select
    Selection.EntireColumn.Hidden = True
    Columns("U:U").Select
    Selection.EntireColumn.Hidden = False
    Columns("V:V").Select
    Selection.EntireColumn.Hidden = False
    Columns("W:W").Select
    Selection.EntireColumn.Hidden = True
    Columns("X:X").Select
    Selection.EntireColumn.Hidden = False
    Columns("Y:Y").Select
    Selection.EntireColumn.Hidden = False
    Columns("Z:Z").Select
    Selection.EntireColumn.Hidden = True
    Columns("AA:AA").Select
    Selection.EntireColumn.Hidden = False
    Columns("AB:AB").Select
    Selection.EntireColumn.Hidden = False
    Columns("AC:AC").Select
    Selection.EntireColumn.Hidden = True
    Columns("AD:AD").Select
    Selection.EntireColumn.Hidden = False
    Columns("AE:AE").Select
    Selection.EntireColumn.Hidden = True
    Columns("AF:AF").Select
    Selection.EntireColumn.Hidden = True
    Columns("AG:AG").Select
    Selection.EntireColumn.Hidden = True
    Columns("AH:AH").Select
    Selection.EntireColumn.Hidden = True
    Columns("AI:AI").Select
    Selection.EntireColumn.Hidden = True
    Columns("AJ:AJ").Select
    Selection.EntireColumn.Hidden = False
    Columns("AK:AK").Select
    Selection.EntireColumn.Hidden = False
    Columns("AL:AL").Select
    Selection.EntireColumn.Hidden = False
    Columns("AM:AM").Select
    Selection.EntireColumn.Hidden = False
    Columns("AN:AN").Select
    Selection.EntireColumn.Hidden = False
    Columns("AO:AO").Select
    Selection.EntireColumn.Hidden = True
    Columns("AP:AP").Select
    Selection.EntireColumn.Hidden = False
    Columns("AQ:AQ").Select
    Selection.EntireColumn.Hidden = True
    Columns("AR:AR").Select
    Selection.EntireColumn.Hidden = False
    Columns("AS:AS").Select
    Selection.EntireColumn.Hidden = True
    Columns("AT:AT").Select
    Selection.EntireColumn.Hidden = False
    Columns("AU:AU").Select
    Selection.EntireColumn.Hidden = True
    Columns("AV:AV").Select
    Selection.EntireColumn.Hidden = True
    Columns("AW:AW").Select
    Selection.EntireColumn.Hidden = True
    Columns("AX:AX").Select
    Selection.EntireColumn.Hidden = True
    Columns("AY:AY").Select
    Selection.EntireColumn.Hidden = False
    Columns("AZ:AZ").Select
    Selection.EntireColumn.Hidden = False
    Columns("BA:BZ").Select
    Selection.EntireColumn.Hidden = True
End Sub
 
Last edited by a moderator:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Welcome to the Board!

Please explain/show examples where it is not working as it should.
And make sure that your workbook/worksheet is not protected, and you do not have any merged cells on your worksheet.

Note: In VBA, it is usually NOT necessary to select ranges in order to work with them. As matter as fact, the code usually runs faster if you don't.
You can also easily combine consecutive columns that you are doing the same thing to.

So, this section of code:
VBA Code: 
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = False
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = False
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = False
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = False
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = False
could be simplified down to just this:
VBA Code: 
    Columns("A:E").Hidden = False
and you can do likewise for the rest of your code.
 
Upvote 0
Joe4 said:
Welcome to the Board!

Please explain/show examples where it is not working as it should.
And make sure that your workbook/worksheet is not protected, and you do not have any merged cells on your worksheet.

Note: In VBA, it is usually NOT necessary to select ranges in order to work with them. As matter as fact, the code usually runs faster if you don't.
You can also easily combine consecutive columns that you are doing the same thing to.

So, this section of code:
VBA Code: 
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = False
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = False
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = False
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = False
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = False
could be simplified down to just this:
VBA Code: 
    Columns("A:E").Hidden = False
and you can do likewise for the rest of your code.
Click to expand...
Aww thats is possibly the problem. i do have a few rows/columns merged, and that will explain why it doesnt run properly - thanks Joe :)

is there a way to overcome this? should i do like "Unmerge All" and then "Undo Unmerge All" at the end?
 
Upvote 0
Merged cells are just about the worst feature of Excel, and should be avoided whenever possible.
They cause all sorts of issues for things like this, sorting, VBA, etc.
Most serious programmers avoid them like the plague!

If you are just merging columns across single rows, you can get the same visual effect without all the issues by using the "Center Across Selection" formatting option instead.
See here for details: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
 
Upvote 0
Solution
Joe4 said:
Merged cells are just about the worst feature of Excel, and should be avoided whenever possible.
They cause all sorts of issues for things like this, sorting, VBA, etc.
Most serious programmers avoid them like the plague!

If you are just merging columns across single rows, you can get the same visual effect without all the issues by using the "Center Across Selection" formatting option instead.
See here for details: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
Click to expand...
thanks Joe. I will try and do away with merging of rows
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Help - Existing VBA routine to hide one column hides three?
Replies
4
Views
613
EssKayKay
E
S
  • Solved
Optimizing Macro
Replies
8
Views
1K
sax2play
S
A
  • Solved
every time I run this code the auto filter only filters 1 column any ideals
Replies
1
Views
546
HaHoBe
HaHoBe
D
  • Question
Very minor change in code had a dramatic impact on the speed. Why?
Replies
2
Views
611
deZine
D
J
  • Solved
Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel
Replies
4
Views
751
dmt32
D

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top