Change column width and row height of hidden colum

Question

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

Answer 1

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)