Macro - Columns Are not Hidding / unhiding correctly

[GarethCW]

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:

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

 

[Joe4]

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:

    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:

    Columns("A:E").Hidden = False

and you can do likewise for the rest of your code.

 

[GarethCW]

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:

    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:

    Columns("A:E").Hidden = False

and you can do likewise for the rest of your code.

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?

 

[Joe4]

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

 

[GarethCW]

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

thanks Joe. I will try and do away with merging of rows