Hide columns using VBA in Excel 2010

Question

Based on the value stored in a specific cell such as A1 of a worksheet I would like to hide a number of columns in the worksheet starting with column B.

Examples of what I am trying to do:

If the value of cell A1 = 10, then hide column B plus 10 columns after B
If the value of cell A2 = 11, then hide column B plus 11 columns after B

The difficulty is actually the way Excel (or least my Excel files) uses the alphabet (A, B, ...) for the name of the columns. I have done this on rows before using code like rows("2:" & range("A1").value) and set .hide = true

Excel Developers · Accepted Answer · 2013-02-25 12:04:58Z

3

I wanted to add a comment to Glenn's answer above but don't have enough reputation. What I was going to add was that you don't need to activate a sheet or select the columns, you can simply go ahead and hide the columns:

Worksheets("TheSheet").Columns(2).Resize(, numColumnsToHide).EntireColumn.Hidden = True

answered Feb 25, 2013 at 12:04

Excel Developers

2,8352 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ZygD · Accepted Answer · 2015-06-30 01:33:00Z

2

You can reference columns by their index number as such: Columns(indexnumber) and you can use Resize() to set the number of columns you want to select like so:

Sub HideColumns()
    Dim numColumnsToHide

    numColumnsToHide = Cells(1, 1).Value

    Columns(2).Resize(, numColumnsToHide).Select

    Selection.EntireColumn.Hidden = True
End Sub

Obviously, this code doesn't have any validation of value in A1 so if someone runs HideColumns() without an integer in A1, bad things are going to happen. This also doesn't unhide any hidden columns.

edited Jun 30, 2015 at 1:33

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

answered Feb 24, 2013 at 4:57

Glenn Stevens

1,99814 silver badges21 bronze badges

2 Comments

user2103670 Over a year ago

Thank you very much. It works well. Just an extension to the existed question. What if I want to hide columns from other sheet(s) for example sheeet1? I try Sheets("Sheet1"). Columns(2).Resize(, numColumnsToHide).Select but this reference method is invalid

Glenn Stevens Over a year ago

You need to make Sheet2 the active worksheet. You can do this by adding Worksheets("Sheet2").Activate after you set the value of numCOlumnsToHide but before before the Columns(2).Resize... line

JCarlos · Accepted Answer · 2020-10-23 23:58:26Z

-1

Please to not complicate just do it

Sub ocultaColunas()

Range("D:E").EntireColumn.Hidden = True

End Sub

answered Oct 23, 2020 at 23:58

JCarlos

11 bronze badge

Collectives™ on Stack Overflow

Hide columns using VBA in Excel 2010

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related