Saving a worksheet without empty rows

[Matt Tyler]

Hi,

I wonder if anyone can help with the following annoying problem.

I have a macro that writes hourly reports that may consist of up to
30,000 rows. At the end of each hour the contents of the worksheet are
written to a file. 30,000 rows are then deleted and the process starts
again.

My problem is, after the first hour I always write 30,000 rows to the
file, even if the last X thousand are empty.

Here is a snippet of the code:

Worksheets("StatsRecord").SaveAs Outfile
Worksheets("StatsRecord").Range("A3:U30000").Delete

Can anyone tell me how I can force excel to only write the rows that
are populated?

Thanks

Matt

 

[Guest]

In order to delete the used range:

Worksheets("StatRecord").UsedRange.Delete

This will delete Rows 1 & 2 also.

You could copy these two rows before deleting and paste them after.

 

[Mike Fogleman]

Once the old data has been deleted, save the empty file before new data is
collected. Excel will retain the original "Used Range" in the Undo buffer,
even though it is deleted, until the file is saved or closed.

Mike F

 

[Dave Peterson]

Debra Dalgleish has some techniques (both manual and VBA) for resetting the used
range at:

http://www.contextures.com/xlfaqApp.html#Unused