Hide Columns

[mathsbeauty]

Hello! I am new here. I am learning macros in excel. I am stuck with the following situation. I need macro which:
i) Shows column C and D.
ii) Shows column F but those cells which contain "NO" in column F are not shown.
iii) All other columns are hidden.
Thus the problem is to hide all columns except C, D and F and to hide those cells in F which contains "NO".

I hope to get some help from experts. I will be really thankful for the help that I may get.

 

[Boller]

I think you should be able to do this with the macro recorder.

Just record :- the required filter on column F and the hiding of all columns except C, D, and F.

 

[mathsbeauty]

Hello Boller! Actually I want to learn this for something more general. I need this macro to be used on various worksheets containing different columns. So recording may not help. Thanks for your suggestion.

 

[jasonb75]

Recorded or written, it would still be specific to those exact criteria.

If you want something flexible then you need to include criteria to identify the columns as the correct ones to show / hide.

 

[mathsbeauty]

Hello Jasonb75! Is there any way in which we can identify all other columns except few specified in excel? e.g. Is it possible to write a macro which only shows column F and all other are hidden? By all other I mean that the only one column is shown, even blank columns are hidden.

 

[jasonb75]

That is easily done, but you still need to decide how you're going to 'specify' the few exceptions.

Also, is it always "No" to be hidden in column F? If not, again you need a way to define how to choose the correct criteria.

 

[Boller]

Hello Boller! Actually I want to learn this for something more general. I need this macro to be used on various worksheets containing different columns. So recording may not help. Thanks for your suggestion.

This requirement is a lot different from your original post.

If you really want to learn VBA, start by recording what you want based on your original post and then learn from the code that is produced.

If you cannot then adjust the code obtained to fit your general requirements, post again

 

[jasonb75]

What you asked for would be something like

Sub test()
    Cells.EntireColumn.Hidden = True
    Range("C:D", "F").EntireColumn.Hidden = False
    ActiveSheet.[F1].AutoFilter Field:=1, Criteria1:="<>no"
    Application.Goto [a1]
End Sub

although I'm not convinced that what you really want will be anywhere near that.

 

[Boller]

What you asked for would be something like

Sub test()
    Cells.EntireColumn.Hidden = True
    Range("C:D", "F").EntireColumn.Hidden = False
    ActiveSheet.[F1].AutoFilter Field:=1, Criteria1:="<>no"
    Application.Goto [a1]
End Sub

although I'm not convinced that what you really want will be anywhere near that.

Which is what he could get from the macro recorder.

 

[mathsbeauty]

Sorry, Bollard. It does not work. Thanks for your efforts.