Unable to Hide a column in Excel 97-2003 workbook

Question

I am trying to hide a column A1 in my sheet using vba. But am getting a error "unable to set hidden property of range class"

Here is my code:

  ActiveWorkbook.Sheets("Project").Activate
   ActiveSheet.Unprotect password

   Dim cmt As comment
   Dim iRow As Integer

   For iRow = 1 To Application.WorksheetFunction.CountA(Columns(1))
      Set cmt = Cells(iRow, 1).comment
         If Not cmt Is Nothing Then

            Cells(iRow + 1, 1) = Cells(iRow, 1).comment.Text
            Cells(iRow, 1).comment.Delete
         Else
         MsgBox "No Comments"
         End If
   Next iRow

   MsgBox ActiveSheet.ProtectionMode

   ActiveSheet.Columns(1).Select

   Selection.EntireColumn.Hidden = True

Am getting error in the line

Selection.EntireColumn.Hidden = True

I have included MsgBox to check whether the sheet is protected and is there any comment available in the cells of that column.

1st MsgBox returns as No Comments and 2nd returns as false.

So the sheet is not protected and comment is also not present.

Confused on why getting the error eventhough.

Please help me out

UPDATE:

I have changed my code like this:

 ActiveWorkbook.Sheets("Project").Activate

    Dim sh As Shape
    Dim rangeToTest As Range
    Dim lRow As Long
    Dim c As Range

    lRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

    Set rangeToTest = ActiveSheet.Range("A1:A" & lRow)
        For Each c In rangeToTest

            For Each sh In ActiveSheet.Shapes
                sh.Delete
            Next sh
        Next c

    ActiveSheet.Range("A1").EntireColumn.Hidden = True

And it worked. But I have added comments to other column headers which i get on hovering mouse over the cell. Am not getting the comments now..

Does deleting shapes have something to do with comments?

score 1 · Accepted Answer · answered Nov 14 '13 at 15:11

Actually i have added comments to other columns in my sheet. Comments come under activesheet.shapes so due to that i am unable to hide the column. Once I have set the placement for that it works perfectly

This code does the trick:

ActiveWorkbook.Sheets(sheetname).Activate

Dim sh As Shape
Dim rangeToTest As Range
Dim lRow As Long
Dim c As Range

lRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

Set rangeToTest = ActiveSheet.Range("A1:A" & lRow)
    For Each c In rangeToTest

        For Each sh In ActiveSheet.Shapes
            sh.Placement = xlMoveAndSize
        Next sh
    Next c

ActiveSheet.Range("A1").EntireColumn.Hidden = True

score 0 · Answer 2 · edited Nov 14 '13 at 12:37

0

You can't use entirecolumn and hidden properties directly on columns, those properties works for Range() object only. Take Range("A1").EntireColumn.Hidden = True

Thanks
Nag

edited Nov 14 '13 at 12:37

Imane Fateh

2,418
3
19
23

answered Nov 14 '13 at 12:04

Nagesh

1
2

changed the line like this: ActiveSheet.Range("A1").EntireColumn.Hidden = True But still same error – Xavier Nov 14 '13 at 12:30

score 0 · Answer 3 · answered Nov 14 '13 at 12:42

0

You should remove this line also

ActiveSheet.Columns(1).Select

answered Nov 14 '13 at 12:42

Nagesh

1
2

score 0 · Answer 4 · answered Nov 14 '13 at 13:23

0

Your code just works fine for me?? I'm in excel 2010, maybe you're not. Plugged it in as is and password protected the sheet as well. Comments or not made no difference, it will hide it whatever.

answered Nov 14 '13 at 13:23

Robbert Koppenol

103
6

Was supposed to be a comment but couldn't place it anywhere. – Robbert Koppenol Nov 14 '13 at 13:30
I have added autofilter to the column which i am trying to hide... will that be an issue? – Xavier Nov 14 '13 at 13:36
No. Did you test it taking it from a sheet having no protection and comments? And only then adding a password and comments consecutively. – Robbert Koppenol Nov 14 '13 at 14:08

score 0 · Answer 5 · edited May 23 '17 at 12:28

Two things

INTERESTING READ
Don't use Application.WorksheetFunction.CountA(Columns(1)) to find the last row. See THIS link on how to find the last row.

Is this what you are trying (UNTESTED)?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim cmt As Comment
    Dim iRow As Long, lRow As Long
    Dim Password As String

    '~~> Change as applicable
    Password = "Blah Blah"

    Set ws = ThisWorkbook.Sheets("Project")

    With ws
        .Unprotect Password

        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For iRow = 1 To lRow
            Set cmt = .Cells(iRow, 1).Comment
            If Not cmt Is Nothing Then
                .Cells(iRow + 1, 1) = .Cells(iRow, 1).Comment.Text
                .Cells(iRow, 1).Comment.Delete
            Else
                'MsgBox "No Comments"
                Debug.Print "No Comments"
            End If
        Next iRow

        .Columns(1).EntireColumn.Hidden = True

        .Protect Password
    End With
End Sub

Unable to Hide a column in Excel 97-2003 workbook

5 Answers5

Linked