Excel: Avoiding The Creation of A Bloated Excel File


I have this code that will: a) create a new workbook b) filter the source data in a second open workbook c) copy the filtered results to the empty worksheet (ws_data) in the new workbook The code runs well, however, the resulting new file is heavily bloated! With a (visible) range of data occupying A1:W71, the file is 20059kb in size. CTRL-END shows the last cell of the range to be W1048208. Is anyone able to comment on what may be causing this bloat. It must be the way data is being copied and pasted between workbooks. The source file is only 149kb. Is there a better way to to this avoiding whatever is causing the bloat? If not, a means to reduce the size of the file to a more manageable one. Deleting the rows each time a new workbook is created may be an awkward and time consuming task, so I'd prefer to take a preventative approach rather than reactive if I could.


        For x = 1 To intCount '{2}
            .Range("AH" & x) = DateValue(Right(.Range("AG" & x), 6))
            'trgt_date = .Range("AH" & x)
            trgt_date = "8/11/2016"
            str_nwb = Format(trgt_date, "MMM-DD (DDD)") & " schedule_1.xlsx"
            Workbooks.Add
            With ActiveWorkbook
                Sheets("Sheet1").Name = "DATA"
                Sheets("Sheet2").Name = "STAFF"
                Sheets("Sheet3").Name = "DEV"
                .SaveAs "H:\PWS\Parks\Parks Operations\Sports\Sports17\DATA\" & str_nwb
                Set wb_nwb = Workbooks(str_nwb)
                Set ws_data = wb_nwb.Worksheets("DATA")
                Set ws_staff = wb_nwb.Worksheets("STAFF")
                Set ws_dev = wb_nwb.Worksheets("DEV")
            End With
            Windows(str_nwb).Visible = False
            'filter database
            With ws_sched
                .Range("A1").AutoFilter _
                    Field:=2, _
                    Criteria1:=trgt_date, _
                    VisibleDropDown:=False
                Set srng = .Cells.SpecialCells(xlCellTypeVisible)
                srng.Copy ws_data.Range("A1")
                If ws_sched.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
            End With


This question generated 17 answers. To proceed to the answers, click here.

This thread is current as of February 23, 2017.


For more resources for Microsoft Excel