无法隐藏Excel 97-2003工作簿中的列 [英] Unable to Hide a column in Excel 97-2003 workbook

查看:140
本文介绍了无法隐藏Excel 97-2003工作簿中的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图使用vba在我的工作表中隐藏列A1。但是出现错误无法设置范围类的隐藏属性

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"

这是我的代码:

  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

p>

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.

第一个MsgBox返回为No Comments,第二个返回为false。

表单不受保护,评论也不存在。

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

困惑于为什么收到错误eventhough。

Confused on why getting the error eventhough.

请帮助我出来

更新:

我已经改变了我的代码:

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?

推荐答案

其实我已经在我的工作表中的其他列添加了评论。评论来自activesheet.shapes,因为我无法隐藏列。一旦我设置了它的位置,它的工作完美

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

这段代码的诀窍:

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

这篇关于无法隐藏Excel 97-2003工作簿中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆