VBA - 检查命名范围是否被隐藏。如果未隐藏，AutoFit 单元格行高

Question

我有一个绑定(bind)到 SharePoint 列表的动态表。在任何给定时间，对于电子表格上生成的报告，只有 1 列可见。由于表格可能会增大或缩小，我需要一个例程来调整任意数量的行，然后查看每一行以确定该行的单元格是否可见。

如果单元格可见，我需要根据行高自动调整例程。

我已经能够让它在静态范围内工作，但似乎无法在命名范围内工作。

我构建了运行两个嵌套循环的例程:一个向下查看行，第二个嵌套循环向右查看列。

我收到的错误是:“运行时错误‘438’:对象不支持此属性或方法”。

错误发生在这里:

如果 Worksheets("owssvr").rowcurrange.EntireColumn.Hidden = False Then

非常感谢任何帮助!

谢谢!

Public Sub AutoFit()
'Sub to autofit the contents of the desired field in the report.
'The routine loops through the table to determine the visible cells, and if visible, autofits the contents

Dim lastrow As Long 'lastrow of table
Dim rowNumber As Long 'counter to determine current visible row number of table
Dim columnrange As Range 'full column range of table
Dim cell As Range 'range used to check if row is visible
Dim rowrange As Range 'loops through row range to determine if current cell is hidden, if so autofit
Dim rowcurrange As Range 'current cell of rowrange

rowNumber = 4
lastrow = Worksheets("owssvr").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set columnrange = Worksheets("owssvr").Range("A" & rowNumber & ":A" & lastrow) 'sets range to check autofit

For Each cell In columnrange 'loops through and checks to see if current row is visible

    Set rowrange = Worksheets("owssvr").Range("G" & rowNumber & ":AR" & rowNumber) 'set current row to dnyamically autofit

    For Each rowcurrange In rowrange 'loops through current row to check if row is hidden, if visible autofit rowcurrange
    MsgBox (rowcurrange)
    If Worksheets("owssvr").rowcurrange.EntireColumn.Hidden = False Then

        Worksheets("owssvr").rowcurrange.Rows.AutoFit 'autofits only the field cell
        MsgBox (rowcurrange)

    End If
    Next

    rowNumber = rowNumber + 1
Next

End Sub

Answer 1

将虚线更改为

If rowcurrange.EntireColumn.Hidden = False Then

您超出了指定范围。 rowcurrange 已经具有必要的工作表信息。该错误是由于 Worksheets("owssvr") 没有名为 rowcurrange 的方法。

命名范围方法实际上看起来像这样。你有一个范围对象。

If Worksheets("owssvr").range("MyNamedRange").EntireColumn.Hidden = False` Then