Avoiding The Creation of A Bloated Excel File

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Code: 
        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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Have you tried limiting srng to the usedrange of the source ?
 
Upvote 0
If not, a means to reduce the size of the file to a more manageable one.
Click to expand...
If no one else posts one I'll post a macro tonight to delete the excess ranges.
 
Upvote 0
try change srng to the below

Code: 
Set srng = .Range(.Cells(1, 1), .Cells(.Range("A" & Rows.Count).End(xlUp).Row, .Cells(1, Columns.Count).End(xlToLeft).Column))
 
Upvote 0
Thank you all for your replies! It nice to see people open to helping.
From my limited testing so far, BarryL's recommendation seems to be working. :)
 
Upvote 0
It appears, I may have been premature. I swear it worked, but now I am unable to get anywhere.
With BarryL's code, srng appears to only want to include row 1, the header row, and not any of the filtered rows below it.
:(
 
Upvote 0
assuming the used range of the source makes sense, try
Code: 
.UsedRange.Copy ws_data.Range("A1")
 
Last edited:
Upvote 0
Hi NoSparks ... sorry for the tardy acknowledgment.
Unfortunately, I'm still getting the 20065KB file being created. the effort is greatly appreciated.
 
Upvote 0
If you still have problems and need a reactive approach, use
For 1 sheet

Code: 
Public Sub MM1()
    ActiveSheet.UsedRange
End Sub


for multiple sheets

Code: 
Public Sub MM2()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
2K
HeinrichPaul
H
B
  • Question
How do i open a csv file created in excel and do a find and replace of this file and save as a new file without strange characters ??
Replies
1
Views
606
Special-K99
S
E
  • Solved
[VBA] File saving does not work/new version not uploaded
Replies
4
Views
487
NdNoviceHlp
NdNoviceHlp
U
  • Question
VBA to look for an open file
Replies
1
Views
506
NateSC
NateSC
D
  • Question
creat columns of text inside xml files in excell
Replies
0
Views
405
Dpalkon
D

Forum statistics

Threads
1,214,553
Messages
6,120,182
Members
448,948
Latest member
spamiki

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top