excel - 更改隐藏列和行的列宽和行高(保持隐藏): Excel VBA

Question

我有一个宏可以更改我的 excel 工作簿中所有工作表的列宽和行高，但是，这个宏不会更改隐藏的行和列。

请建议我应该如何修改我的代码，以便它应该改变隐藏行和列的列宽和行高并保持它们隐藏？

Sub rowcolactivesheetb()

    Dim exworkb As Workbook
    Dim xlwksht As Worksheet
    Dim lastrow1 As Long
    Dim lastcolumn1 As Long
    Dim firstrowDB As Long

    With ActiveSheet

        lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
        lastcolumn1 = .Cells(1, Columns.Count).End(xlToLeft).Column

        .Range(.Cells(1, 1), .Cells(lastrow1, lastcolumn1)).Select

        With Selection.SpecialCells(xlCellTypeVisible)
            .ColumnWidth = 10.2
            .RowHeight = 9.4
        End With

    End With

End Sub

编辑

我已经在下面实现了 Wolfie 的方法，但现在我得到了

Run-time error 91, Object variable or With block variable not set.

在这条线上:

' Z is a number, my loop variable for looping over each sheet
rng = ActiveWorkbook.Sheets(Z).Range(Sheets(Z).Cells(1, 3), Sheets(Z).Cells(lastrow1, lastcolumn1))

Answer 1

下面的代码相当简单，并评论了更多细节。脚步:

循环遍历使用范围内的行和列，注意哪些是隐藏的。

取消隐藏所有内容并调整大小

循环遍历行和列，隐藏在

之前隐藏的那些

代码 :

Sub rowcolactivesheetb()
' Resizes all rows and columns, including those which are hidden.
' At the end, hidden rows and columns remain hidden.
    Dim n As Long
    Dim hiddencols() As Long
    Dim hiddenrows() As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    With ThisWorkbook.ActiveSheet
        ' Set up range variable and true/false hidden arrays        
        ' We don't need to find last row/col, just used UsedRange
        Set rng = .UsedRange
        ReDim hiddencols(rng.Columns.Count)
        ReDim hiddenrows(rng.Rows.Count)
        ' Get hidden/visible status of each row and column
        For n = 0 To UBound(hiddencols)
            hiddencols(n) = rng.Columns(n + 1).Hidden
        Next n
        For n = 0 To UBound(hiddenrows)
            hiddenrows(n) = rng.Rows(n + 1).Hidden
        Next n
        ' Unhide all
        rng.EntireColumn.Hidden = False
        rng.EntireRow.Hidden = False
        ' resize all
        rng.ColumnWidth = 10.2
        rng.RowHeight = 9.4
        ' Re-hide rows/cols
        For n = 0 To UBound(hiddencols)
            rng.Columns(n + 1).Hidden = hiddencols(n)
        Next n
        For n = 0 To UBound(hiddenrows)
            rng.Rows(n + 1).Hidden = hiddenrows(n)
        Next n
    End With
    Application.ScreenUpdating = True
End Sub

最后是关于 With 的注释, 你不应该开始第二个 With block ，除非它是用于第一个对象中的对象。但实际上你本可以放弃(不受欢迎的) Select无论如何使用这个事实......

With ActiveSheet
    lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
    lastcolumn1 = .Cells(1, Columns.Count).End(xlToLeft).Column
    With .Range(.Cells(1, 1), .Cells(lastrow1, lastcolumn1)).SpecialCells(xlCellTypeVisible)
        .ColumnWidth = 10.2
        .RowHeight = 9.4
    End With
End With

编辑:

关于您的后续错误，您必须使用 Set分配 Range 时的命令对象为变量。所以你的代码应该是

Set rng = ActiveWorkbook.Range("...

您不必使用 Set用于基本变量类型(字符串、整数等)