当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - Unable to Hide a column in Excel 97-2003 workbook

问题描述:

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?

网友答案:

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
网友答案:

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

网友答案:

You should remove this line also

ActiveSheet.Columns(1).Select

网友答案:

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.

网友答案:

Two things

  1. INTERESTING READ

  2. 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
分享给朋友:
您可能感兴趣的文章:
随机阅读: