Hiding Columns Question

[EOAEvan]

I have picked up two different types of code to hide columns through searching this site. However neither are working the way I am tring to use them... Here is a sample of my code. (i realize both lines are attempting the same thing.. I have been commenting out one line to try the other.

Set ssht = Workbooks("Account Lookup Table - Dept ROF File Output").Sheets("ROF Col")
Set dsht = Workbooks("Collections - Central").Sheets("Feb 09")
With dsht
    .Unprotect Password:=""
    Columns("D,G,H,L,O,P,T,W,X,AB,AE,AD,AV,AM,AN,AR,AU,AV").Hidden = True
    Range("D,G,H,L,O,P,T,W,X,AB,AE,AD,AV,AM,AN,AR,AU,AV").EntireColumn.Hidden = True

Now if I change them to this they work... I just figure there has to be a better way.

Set ssht = Workbooks("Account Lookup Table - Dept ROF File Output").Sheets("ROF Col")
Set dsht = Workbooks("Collections - Central").Sheets("Feb 09")
With dsht
    .Unprotect Password:=""
    Columns("D").Hidden = True
    Columns("G").Hidden = True
    Columns("H").Hidden = True
    'and so on
    'or
    Range("D:D,G:H,L:L,O:P,T:T, and so on").EntireColumn.Hidden = True

I understand why the range example is not working because "D" is not a range.. The prefered answer would be to use the Columns("D").Hidden = True method but is there a way to include all of the columns in a single line?

 

[VoG]

Try

Range("D:D,G:G,H:H,L:L,O:O,P:P,T:T,W:W,X:X,AB:AB,AE:AE,AD:AD,AV:AV,AM:AM,AN:AN,AR:AR,AU:AU,AV:AV").EntireColumn.Hidden = True

 

[EOAEvan]

I had gotten that to work.. just didint know if there was another way. Ill stick with that one..

Thanks!!

 

[EOAEvan]

Follow up question... I would now like to add a msg box at the begining of this macro (what is posted here is only the first portion) that if the user selects yes it will run the unhide columns portion, if they select no it will not.. All other portions of the macro should run. So if I select NO on the msg box it will skip the bolded portion of the code.. There will be several other places this happens further down the macro. The msg box should apply to all of them.

Set ssht = Workbooks("Account Lookup Table - Dept ROF File Output").Sheets("ROF Col")
Set dsht = Workbooks("Collections - Central").Sheets("Feb 09")
With dsht
    .Unprotect Password:=""

With ssht
    .Range("M4:M106").Copy dsht.Range("J4:J106")
    .Range("N4:N106").Copy dsht.Range("L4:L106")
    .Range("O4:O106").Copy dsht.Range("R4:R106")
    .Range("P4:P106").Copy dsht.Range("T4:T106")
    .Range("Q4:Q106").Copy dsht.Range("Z4:Z106")
    .Range("R4:R106").Copy dsht.Range("AB4:AB106")
    .Range("S4:S106").Copy dsht.Range("AH4:AH106")
    .Range("T4:T106").Copy dsht.Range("AJ4:AJ106")
    .Range("U4:U106").Copy dsht.Range("AP4:AP106")
    .Range("V4:V106").Copy dsht.Range("AR4:AR106")
End With
    Range("D:D,G:H,L:L,O:P,T:T,W:X,AB:AB,AE:AF,AV:AV,AM:AN,AR:AR,AU:AV").EntireColumn.Hidden = False
    Cells.Locked = True
    .Protect Password:=""
End With

 

[VoG]

Try

Dim Ans As VbMsgBoxResult
Ans = MsgBox("Do you want to do this stuff?", vbQuestion)
Set ssht = Workbooks("Account Lookup Table - Dept ROF File Output").Sheets("ROF Col")
Set dsht = Workbooks("Collections - Central").Sheets("Feb 09")
With dsht
    .Unprotect Password:=""

With ssht
    .Range("M4:M106").Copy dsht.Range("J4:J106")
    .Range("N4:N106").Copy dsht.Range("L4:L106")
    .Range("O4:O106").Copy dsht.Range("R4:R106")
    .Range("P4:P106").Copy dsht.Range("T4:T106")
    .Range("Q4:Q106").Copy dsht.Range("Z4:Z106")
    .Range("R4:R106").Copy dsht.Range("AB4:AB106")
    .Range("S4:S106").Copy dsht.Range("AH4:AH106")
    .Range("T4:T106").Copy dsht.Range("AJ4:AJ106")
    .Range("U4:U106").Copy dsht.Range("AP4:AP106")
    .Range("V4:V106").Copy dsht.Range("AR4:AR106")
End With
    If Ans = vbYes Then Range("D:D,G:H,L:L,O:P,T:T,W:X,AB:AB,AE:AF,AV:AV,AM:AN,AR:AR,AU:AV").EntireColumn.Hidden = False
    Cells.Locked = True
    .Protect Password:=""
End With

 

[EOAEvan]

Genius! thanks

 

[EOAEvan]

Ok, running into a problem.. Using the below code it will error on the line trying to unhide the columns. However if i make the dsht active and continue to step through the code it works just fine.... Why does the sheet have to be the active sheet? Isnt this line of code part of the With dsht statement and therefore should be assigned to just act on the correct sheet?

The error it is giving is: Run-time error 1004 Unable to set the Hidden property of the Range class.

Set ssht = Workbooks("Account Lookup Table - Dept ROF File Output").Sheets("ROF Col")
Set dsht = Workbooks("Collections - Central").Sheets("Feb 09")
With dsht
    .Unprotect Password:=""

With ssht
    .Range("M4:M106").Copy dsht.Range("J4:J106")
    .Range("N4:N106").Copy dsht.Range("L4:L106")
    .Range("O4:O106").Copy dsht.Range("R4:R106")
    .Range("P4:P106").Copy dsht.Range("T4:T106")
    .Range("Q4:Q106").Copy dsht.Range("Z4:Z106")
    .Range("R4:R106").Copy dsht.Range("AB4:AB106")
    .Range("S4:S106").Copy dsht.Range("AH4:AH106")
    .Range("T4:T106").Copy dsht.Range("AJ4:AJ106")
    .Range("U4:U106").Copy dsht.Range("AP4:AP106")
    .Range("V4:V106").Copy dsht.Range("AR4:AR106")
End With
    If Ans = vbYes Then Range("D:D,G:H,L:L,O:P,T:T,W:X,AB:AB,AE:AF,AV:AV,AM:AN,AR:AR,AU:AV").EntireColumn.Hidden = False  ' This is where the error is occuring
    If AnsLck = vbYes Then Cells.Locked = True
    .Protect Password:=""
End With

 

[VoG]

You are missing a dot to qualify the range

If Ans = vbYes Then .Range("D:D,G:H,L:L,O:P,T:T,W:X,AB:AB,AE:AF,AV:AV,AM:AN,AR:AR,AU:AV").EntireColumn.Hidden = False
If AnsLck = vbYes Then .Cells.Locked = True