Hiding the Range of Columns in Excel Using VBA Based on Cell Values

advertisements

I need to hide a range of cells using a macro in excel. C11 contains the column index from where I need to start hiding the columns.

Sub test()

Dim i As Integer
Dim j As Integer

Dim rocket As Range

i = Range("c11").Value
j = 12

rocket = Range(Cells(5, i), Cells(5, j))

Range("Rocket").Select

Selection.EntireColumn.Hidden = True

End Sub

The code is giving some unexpected error and as I am a novice, so have no clue what needs to be done..


Tree steps to make your code working:

1st. Add Set key word in appropriate line which is necessary:

Set rocket = Range(Cells(5, i), Cells(5, j))

2nd. Rocket variable represents range, you will NOT need to call it in this way:

Range("Rocket")....

but

rocket....

3rd. Avoid Select method and Selection object always when possible. Therefore the last two lines replace with this single one (which implements 2nd step, too):

rocket.EntireColumn.Hidden = true