CODEWITHSUNDEEP
excelvba
Nik
Nik

Reputation: 21

How to hide columns in a VBA named range?

I'm pretty new to VBA and I'm trying to hide some columns in workbooks. I have to do it in VBA because the workbook is an Analysis for Office workbook, so I have to hide some columns that the final users don't have to see. My problem is that I can't point to a specific letter of the columns, because, since the workbook is modifiable like a pivot with other dimensions in respect of the default ones with which it opens, if I point to a specific column in the code, it won't be the same when an user add new dimensions. So I have to find the column with its name ad hide it.

So for now I tried this: 

Sub Hide_Columns()
'Dim Sourcecell As Range
Dim wb As Workbook
Dim ws As Worksheet
'Set Sourcecell = Nothing
'Set Sourcecell = ThisSheet.Range("SapCrosstab1").Find("Colonna di calcolo", LookIn:=xlFormulas)
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Demand Planning")

    wb.Sheets("Demand Planning").Range("Colonna di calcolo").EntireColumn.Hidden = True
End Sub

The problem here is that I'm having the

"Run-time error 1004: Application-defined or object-defined error"

and I don't know how to fix it. I commented that 3 lines because I'm not so sure they could help.

Upvotes: 0

Views: 1118

Answers (1)

FunThomas
FunThomas

Reputation: 29181

Your current attempt fails because Range("Colonna di calcolo") will not search for a column with that header. Instead, it want to access a Named Range with that name, and if you don't define this, it will throw exactly this error 1004.

I think your attempt with SourceCell was not bad - issue a Find command and hide the EntireColumn of the found cell. Your mistake was that there is no object ThisSheet. There are objects ThisWorkbook which refers to the entire workbook where the code is stored, or ActiveWorkbook which is the Workbook that is currently shown (not necessarily the workbook where the macro is stored).

You can solve your issue either by defining a Named Range for the column - in that case your code should work.
Or change the code to something like

Dim ws as Worksheet
Set ws = ThisWorkbook.Sheets("Demand Planning")

Dim Sourcecell as Range
Set Sourcecell = ws.Range("1:1").Find("Colonna di calcolo", LookIn:=xlFormulas)
If Not Sourcecell Is Nothing Then
    Sourcecell.EntireColumn.Hidden = True
End If

Upvotes: 1

Related Questions