Recorded macro not working the way i thought?

Troy1979

New Member
Joined
Oct 27, 2017
Messages
2
I am looking to some help to understand why i am having an issue with a recorded macro.
I want to hide multiple columns and have recorded a macro as below. My problem becomes that when I run the macro, it hides all columns from R:AZ but there are specific column i want left un-hidden.

Sub HIDE_FY18_BUDGETS()
'
' HIDE_FY18_BUDGETS Macro
'


'
Range("R:R,S:S,U:U,V:V,X:X,Y:Y,AA:AA,AB:AB,AD:AD,AE:AE,AG:AG,AH:AH").Select
Range("AH1").Activate
ActiveWindow.SmallScroll ToRight:=17
Range( _
"R:R,S:S,U:U,V:V,X:X,Y:Y,AA:AA,AB:AB,AD:AD,AE:AE,AG:AG,AH:AH,AJ:AJ,AK:AK,AM:AM,AN:AN,AP:AP,AQ:AQ,AS:AS,AT:AT,AV:AV,AW:AW,AY:AY" _
).Select
Range("AZ1").Activate
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 1
Selection.EntireColumn.Hidden = True
End Sub
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
Take a look at this.
I have combined certain columns, as pairs, that I assume you want to hide.
The green in the code won't be seen by VBA. I've also take out some other unnecessary lines.

In effect, all that I assume you need to hide is the columns within the range which is in black, yes?
Code:
Sub HIDE_FY18_BUDGETS()
[COLOR=#00ff00]'
' HIDE_FY18_BUDGETS Macro
'


'
'Range("R:S,U:V,X:Y,AA:AB,AD:AE,AG:AG,AH:AH").Select
'Range("AH1").Activate[/COLOR]

Range("R:S,U:V,X:Y,AA:AB,AD:AE,AG:AH,AJ:AK,AM:AN,AP:AQ,AS:AT,AV:AW, AY:AY").Select
[COLOR=#00ff00]'Range("AZ1").Activate
[/COLOR]
Selection.EntireColumn.Hidden = True
End Sub
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
Hi Troy,

Welcome to MrExcel!!

Which columns do you want left visible (I think it's the first line of code but I'm not sure).

Thanks,

Robert
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,802
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
Sub Hide_Columns()
Application.ScreenUpdating = False
For i = 23 To 1 Step -1
    Cells(1, Choose(i, "R", "S", "U", "V", "X", "Y", "AA", "AB", "AD", "AE", "AG", _
    "AH", "AJ", "AK", "AM", "AN", "AP", "AQ", "AS", "AT", "AV", "AW", "AY")).EntireColumn.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
 

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603

ADVERTISEMENT

In fact, all black code can be replaced with one simple line:

Range("R:S,U:V,X:Y,AA:AB,AD:AE,AG:AH,AJ:AK,AM:AN,AP:AQ,AS:AT,AV:AW, AY:AY").EntireColumn.Hidden = True
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
If it's the columns BrianJN1 suggests, you do it in a single line like so:

Code:
Range("R:S,U:V,X:Y,AA:AB,AD:AE,AG:AH,AJ:AK,AM:AN,AP:AQ,AS:AT,AV:AW,AY:AY").EntireColumn.Hidden = True

You can of course change these column references to suit.

Robert
 

Troy1979

New Member
Joined
Oct 27, 2017
Messages
2
Thanks guys, this has worked :)

Take a look at this.
I have combined certain columns, as pairs, that I assume you want to hide.
The green in the code won't be seen by VBA. I've also take out some other unnecessary lines.

In effect, all that I assume you need to hide is the columns within the range which is in black, yes?
Code:
Sub HIDE_FY18_BUDGETS()
[COLOR=#00ff00]'
' HIDE_FY18_BUDGETS Macro
'


'
'Range("R:S,U:V,X:Y,AA:AB,AD:AE,AG:AG,AH:AH").Select
'Range("AH1").Activate[/COLOR]

Range("R:S,U:V,X:Y,AA:AB,AD:AE,AG:AH,AJ:AK,AM:AN,AP:AQ,AS:AT,AV:AW, AY:AY").Select
[COLOR=#00ff00]'Range("AZ1").Activate
[/COLOR]
Selection.EntireColumn.Hidden = True
End Sub
 

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
Trebor79's version will certainly work, it is actually tidier.
I did consider it but I left it as two lines to indicate to you what lines I was taking out of the macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,677
Messages
5,524,225
Members
409,566
Latest member
santoshsj

This Week's Hot Topics

Top