File Size Increase Controlled, Question
 

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

File Size Increase Controlled, Question
 

Hi
you may check out the following site to reset your used range:
http://www.contextures.com/xlfaqApp.html#Unused

maybe this will help to clear your workbook of unused ranges


--
Regards
Frank Kabel
Frankfurt, Germany

ron wrote:
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