更改隐藏列和行的列宽度和行高（保持隐藏）：Excel VBA [英] Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA

问题描述

我有一个宏改变我的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 =。单元格（1，Columns.Count）.End（xlToLeft）.Column 
 
 .Range（.Cells（1，1），.Cells（lastrow1，lastcolumn1））选择
 
 With Selection.SpecialCells（xlCellTypeVisible）
 .ColumnWidth = 10.2 
 .RowHeight = 9.4 
结束
 
结束Wit h 
 
 End Sub 
  

---

编辑

我已经实现了Wolfie的方法，但现在正在获得

运行时错误91，对象变量或块变量未设置。

/ p>

 'Z是一个数字，我的循环变量用于循环每个表
 rng = ActiveWorkbook.Sheets（Z）。 （（Z）.Cells class =h2_lin>解决方案

以下代码相当简单，并进一步详细说明。步骤：

• 循环使用范围中的行和列，注意隐藏哪些。


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


• 循环返回行和列，隐藏

代码：

  Sub rowcolactivesheetb（）
'调整所有行和列的大小，包括隐藏的那些。 
'最后，隐藏的行和列保持隐藏。 
 Dim n As Long 
 Dim hiddencols（）As Long 
 Dim hiddenrows（）As Long 
 Dim rng As Range 
 Application.ScreenUpdating = False 
 With ThisWorkbook.ActiveSheet 
'设置范围变量和true / false隐藏数组
'我们不需要找到最后一行/ col，刚刚使用UsedRange 
设置rng = .UsedRange 
 ReDim hiddencols（rng.Columns.Count）
 ReDim hiddenrows（rng.Rows.Count）
'获取每行和列的隐藏/可见状态
对于n = 0对于UBound（hiddencols ）
 hiddencols（n）= rng.Columns（n + 1）。隐藏
下一个n 
对于n = 0到UBound（hiddenrows）
 hiddenrows（n）= rng。行（n + 1）。隐藏
下一个n 
'取消隐藏所有
 rng.EntireColumn.Hidden = False 
 rng.EntireRow.Hidden = False 
' 
 rng.ColumnWidth = 10.2 
 rng.RowHeight = 9 .4 
'重新隐藏行/ cols 
对于n = 0到UBound（hiddencols）
 rng.Columns（n + 1）.Hidden = hiddencols（n）
下一步n 
对于n = 0到UBound（hiddenrows）
 rng.Rows（n + 1）.Hidden = hiddenrows（n）
下一个n 
结束
应用程序.ScreenUpdating = True 
 End Sub 
  

---

最后一个关于的注释，使用，不应该开始第二个使用块，除非是用于 第一个。但是，真的你可以用这个事实来贬低（不需要的）选择 ...

 code> 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））。SpecialCells（xlCellTypeVisible）
 .ColumnWidth = 10.2 
 .RowHeight = 9.4 
结束
结束
  

---

编辑：

关于您的跟进错误，您必须在分配$ $ c时使用 Set 命令$ c> Range 对象变量。所以你的代码应该是

 设置rng = ActiveWorkbook.Range（... 
  

您不必使用将设置为基本变量类型（字符串，整数等） ）

I have a macro which changes column width and row height of all the worksheets in my excel workbook, however, this macro is not making the changes in the hidden rows and column.

Please suggest how should I modify my code so that it should change the column width and row height of hidden rows and columns and keep them hidden?

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

---

Edit

I have implemented Wolfie's method below, but am now getting

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

on this line:

' 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))

解决方案

The below code is fairly straight-forward, and commented for further details. Steps:

• Loop through rows and columns in the used range, note which ones are hidden.
• Unhide everything and resize
• Loop back through rows and columns, hiding those which were hidden before

Code:

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

---

Lastly a note on With, you should not start a second With block unless it is for an object within the first one. But really you could have ditched the (undesirable) Select using that fact anyway...

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

---

Edit:

With respect to your follow up error, you must use the Set command when assigning a Range object to a variable. So your code should be

Set rng = ActiveWorkbook.Range("...

You don't have to use Set for fundamental variable types (Strings, Integers, etc)

这篇关于更改隐藏列和行的列宽度和行高（保持隐藏）：Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！