Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA

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

If you edit a hidden row and column they will become visible so you need to hide them again — 0m3r
Thats the main problem can it be hidden automatically by applying any code that it changes the row width and height and becomes hidden again — Stacey
In my excel sheet apart from hidden rows and columns there are grouped rows and columns as well if is it possible to modify my code that it works for the grouped rows and columns (if not hidden rows and columns) — Stacey

Wolfie Wolfie · Accepted Answer · 2017-05-01T09:08:15

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)

Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA

1 Answers