时间戳

首页 > 解决方案 > 不要调整隐藏行的大小

excel - 不要调整隐藏行的大小

问题描述

我想让一个范围内的所有行都具有一定的高度，除了隐藏的行。一旦我的代码到达.SpecialCells (xlCellTypeVisible)它就会跳转到 a Private Sub()，然后返回并.RowHeight = 12更改我的所有行，包括隐藏的行。如何防止它调整隐藏的行？

Sub CreateSched()
    
    Worksheets("Schedule").Activate
    
    Sheets("Schedule").Unprotect
    
    Dim t As Range, u As Range
    Set u = Range("F6:F282")
    
        Application.ScreenUpdating = False

        For Each t In u
            If Not IsEmpty(t.Value) And t = 0 Then
                t.EntireRow.Hidden = True
            ElseIf t > 0 Then
                t.EntireRow.Hidden = False
            End If
        Next t
        
    Application.ScreenUpdating = True
    
    Columns("B:AA").AutoFit
    
    With Worksheets("Schedule").Range("E1:E304")
        .SpecialCells (xlCellTypeVisible)
        .RowHeight = 12
    End With
    
    Sheets("Schedule").Protect
End Sub

标签： excelvba

解决方案

试试这个 - 一些避免选择/激活的建议（通常这不是必需的）

Sub CreateSched()
    
    Dim t As Range
    
    With Worksheets("Schedule")
        Application.ScreenUpdating = False
        .Unprotect
        For Each t In .Range("F6:F282").Cells
            If Not IsEmpty(t.Value) And t = 0 Then
                t.EntireRow.Hidden = True
            ElseIf t > 0 Then
                t.EntireRow.Hidden = False
            End If
        Next t
        On Error Resume Next '<< in case no visible rows
        .Range("E1:E304").SpecialCells(xlCellTypeVisible).RowHeight = 12
        On Error Goto 0 'stop ignoring errors
        .Columns("B:AA").AutoFit
        .Protect
        Application.ScreenUpdating = True
    End With
    
End Sub

推荐阅读