Code to Hide Rows adds 5MB to File Size

[Simon Austin]

Hi All.

I have this small piece of code which hides rows in a worksheet dependant upon the data in a cell. It works fine, however it adds 5MB onto the size of the file. If anyone has an alternative method of achieving the same please let me know.

Thanks!

Simon

Sub Hide_Summary_Rows()

'Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Sheets("Contract Config").Select
Sheets("Contract Config").Unprotect ("password")
BeginRow = 1
    EndRow = 129
    ChkCol = 41

Columns("ao:ao").Select
Selection.EntireColumn.Hidden = False

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "Out" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
Columns("ao:ao").Select
Selection.EntireColumn.Hidden = True
Range("a1").Select
Sheets("Contract Config").Activate
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
    
            Application.ScreenUpdating = True
End Sub

[mdbct]

The macro itself is not causing the blot - there is something else going on with your sheet.

Does the bloat happen if you perform the same steps as the macro manually?

Here is a condensed version of your macro - it avoids the selection steps your original macro contained.

Sub Hide_Summary_Rows()
    Dim wsh As Worksheet, RowCnt As Integer
    Dim BeginRow As Integer, endRow As Integer, ChkCol As Integer
    Set wsh = Sheets("Contract Config")
    Application.ScreenUpdating = False
    wsh.Unprotect ("password")
    BeginRow = 1
    endRow = 129
    ChkCol = 41

    wsh.Columns("ao").EntireColumn.Hidden = False

    For RowCnt = BeginRow To endRow
        If wsh.Cells(RowCnt, ChkCol).Value = "Out" Then
            wsh.Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    wsh.Columns("ao").EntireColumn.Hidden = True
    wsh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
                Password:="password"

    Application.ScreenUpdating = True
    wsh.Activate
    Range("A1").Select
End Sub

[Simon Austin]

Thanks.

I've inserted your code and it now executes faster, so thanks for that. I'll go back to an old version pre this code and try manually hiding the rows and see what happens, and then apply your code to it to see the effect on the file size.

Thanks for your time.

Simon