无法隐藏Excel 97-2003工作簿中的列 [英] Unable to Hide a column in Excel 97-2003 workbook
问题描述
我试图使用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屋!