File Size Increase Controlled, Question

  • Thread starter Thread starter ron
  • Start date Start date
R

ron

Hi...I have a workbook with a macro and everytime I ran it, the file
size increased around 400KB, even though no new data was added to the
workbook. I did the last cell check on each worksheet and found it
was right at the edge of my data. I eliminated the macro module and
found no large decrease in file size. I eliminated the worksheets one
by one and found one worksheet MUCH larger in size than the others. I
incorporated some code at the end of the macro to remove this
worksheet and then readd a clean, fresh one back in, everytime the
macro runs. Now my workbook doesn't grow in size when I run the
macro.

I use the following code to paste web data on the offending worksheet.
I extract the data I want

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://xyz.com, _
Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

and then use the following to clean the worksheet prior to repeating
the process (about 1,500 times each time I run the macro).

Cells.Select
Selection.Delete
Range("A1").Select

Apparently some residue remains on the worksheet. If I do a rows -
delete, then save the workbook, the "crud" still remains. Can anyone
provide me with an explanation as to what this "crud" is?...TIA, Ron
 
Back
Top