Problems hiding/unhiding certain columns using an Excel macro 2

[montypython1]

Greetings,

Can anyone tell me why I can't use an Excel macro to hide or unhide certain columns within my Excel spreadsheet?

I can manually hide or unhide these columns, but when I try to record the keystrokes using an Excel macro, it ends up hiding almost ALL of the columns (it only shows columns A,B,U). I even tried placing a wait command within the VBA script to try to slow down the execution:

    Application.Wait Now + TimeValue("00:00:03")

But this only served to slow down the end result.

Below is the VBA code. You can see that I am selecting and unhiding all columns, then hiding certain columns. It works when I do this manually, but not when I run it in a macro:

Sub CFS_Show_Gross()
'
' CFS_Show_Gross Macro

'
    Cells.Select
    Selection.EntireColumn.Hidden = False

    Range("C:D,F:H,J:L,N:P,R:R").Select
    Range("R3").Activate
    Selection.EntireColumn.Hidden = True
    
    Range("S:T,V:AE").Select
    Range("V1").Activate
    Selection.EntireColumn.Hidden = True
    
    Range("C10").Select

End Sub

For years, I have been able to record my keystrokes and my formatting without any problems. What am I missing?

Thanks,
Dave

 

[SkipVought]

hi,

Works for me.

However, I'd code it this way...

Sub CFS_Show_Gross()
'
' CFS_Show_Gross Macro

'
    Cells.EntireColumn.Hidden = False

    Range("C:D,F:H,J:L,N:P,R:R").EntireColumn.Hidden = True
    
    Range("S:T,V:AE").EntireColumn.Hidden = True
    
    Range("C10").Select

End Sub

And furthermore, i'd run the hide from a table in a loop, rather than hard-coded, but that's me. Your macro works fine on an empty sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[montypython1]

Hi Skip,

Thank you for your suggestion. Your code worked perfectly.

It is strange that MY code worked on YOUR spreadsheet, but the identical code would NOT work on MY spreadsheet. Do you have any idea why that would be?

I'm not sure how to attach a file to Tek-Tips. I was going to attach a small test version of the spreadsheet with both your macro and mine, in case you would like to see what I'm seeing (maybe there is some setting within my spreadsheet that I am not seeing).

Again, thank you for your assistance.

Dave

 

[SkipVought]

How Can I Make My Code Run Faster? faq707-4105

Send me a message thru this FAQ link, and I'll eMail you.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[montypython1]

Will send the message now.
Thanks,
Dave

 

[montypython1]

Hi Skip,

Thank you! You opened my eyes about the limitation of using keystroke recording. Your solution is working perfectly on my big spreadsheet (after testing on the small spreadsheet that I sent you).

Thank you again for all of your assistance.

Dave

 

[SkipVought]

Great, Dave. Glad to help!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}