Hide Columns

[cesymcox]

Currently using the following Sub to hide columns in a worksheet, but continues to hide all columns for H to AA with the ActiveCell. value = "show".
Sub hidecolumns()

Sheets("MTD-Market Summary").Activate
Range("C62", "AB62").Select
Selection.EntireColumn.Hidden = False
Range("C62").Select
Do While ActiveCell.value = "show"
ActiveCell.Offset(0, 1).Select
Loop
Range(ActiveCell, "AA62").Select
Selection.EntireColumn.Hidden = True

 

[Vogateer]

What are you trying to do with this VBA? It looks like this code looks at your ActiveCell then loops through the following columns to select them, then eventually selects the active cell through column AA to hide them.

Sheets("MTD-Market Summary").Activate
Range("C62", "AB62").Select
Selection.EntireColumn.Hidden = False
Range("C62").Select
Do While ActiveCell.value = "show"
ActiveCell.Offset(0, 1).Select
Loop ' Why is there a loop here to select a different cell?
' Range(ActiveCell, "AA62").Select ' This is where it selects a range from your ActiveCell to column AA.
' The commented line above seems wrong, but maybe I'm confused about what you're doing.
Selection.EntireColumn.Hidden = True

 

[Joe4]

Welcome to the Board!

Please explain exactly what you are trying to do, in plain English.
Are you trying to hide all columns between columns C and AB where the value in row 62 of that column is NOT equal to "Show"?

Also, you should NEVER use "Select" within a loop like that. It will unnecessarily slow your loop down (loops are already slow, by definition).
It is seldom necessary to actually select a cell in order to checks its value or work with it.

 

[cesymcox]

Not sure, I took over this file and this was the original VBA attached. What is the significance of having a Loop?

 

[cesymcox]

What are you trying to do with this VBA? It looks like this code looks at your ActiveCell then loops through the following columns to select them, then eventually selects the active cell through column AA to hide them.

Sheets("MTD-Market Summary").Activate
Range("C62", "AB62").Select
Selection.EntireColumn.Hidden = False
Range("C62").Select
Do While ActiveCell.value = "show"
ActiveCell.Offset(0, 1).Select
Loop ' Why is there a loop here to select a different cell?
' Range(ActiveCell, "AA62").Select ' This is where it selects a range from your ActiveCell to column AA.
' The commented line above seems wrong, but maybe I'm confused about what you're doing.
Selection.EntireColumn.Hidden = True

Not sure, I took over this file and this was the original VBA attached. What is the significance of having a Loop?

 

[Vogateer]

Not sure, I took over this file and this was the original VBA attached. What is the significance of having a Loop?

The loop is going to repeat the same instructions above it (After the Do statement) until a condition is met (until the value of the cell is "Show").

I think it would be better if you give us a sample of the relevant part of your spreadsheet (using dummy data if needed), and then describe exactly what you want to do. The code looks like it's doing something simple, but we can come up with something better.

 

[cesymcox]

Welcome to the Board!

Please explain exactly what you are trying to do, in plain English.
Are you trying to hide all columns between columns C and AB where the value in row 62 of that column is NOT equal to "Show"?

Also, you should NEVER use "Select" within a loop like that. It will unnecessarily slow your loop down (loops are already slow, by definition).
It is seldom necessary to actually select a cell in order to checks its value or work with it.

If the value in row 62 + "hide", then I would like the VBA to hide the entire column. This is in preparation for the next directive to pint the report to a PDF.

 

[Joe4]

It sounds like this is all you need:

Sub MyHideColumns()

    Dim cell As Range

    Sheets("MTD-Market Summary").Activate

'   First unhide all columns
    Range("C62:AB62").EntireColumn.Hidden = False

'   Loop through cells C62:AB62 and hide column if cell is "hide"
    For Each cell In Range("C62:AB62")
        If cell.Value = "hide" Then cell.EntireColumn.Hidden = True
    Next cell

End Sub

Note how I did not need to use any "Select" statements in my code. Using "Select" and "ActiveCell" is extremely inefficient within loops.

 

[cesymcox]

It sounds like this is all you need:

Sub MyHideColumns()

    Dim cell As Range

    Sheets("MTD-Market Summary").Activate

'   First unhide all columns
    Range("C62:AB62").EntireColumn.Hidden = False

'   Loop through cells C62:AB62 and hide column if cell is "hide"
    For Each cell In Range("C62:AB62")
        If cell.Value = "hide" Then cell.EntireColumn.Hidden = True
    Next cell

End Sub

Note how I did not need to use any "Select" statements in my code. Using "Select" and "ActiveCell" is extremely inefficient within loops.

That worked! Thank you!

 

[Joe4]

You are welcome.
Glad we were able to help!