Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a worksheet without empty rows
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").Delet e Can anyone tell me how I can force excel to only write the rows that are populated? Thanks Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a worksheet without empty rows
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. "Matt Tyler" wrote: 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").Delet e Can anyone tell me how I can force excel to only write the rows that are populated? Thanks Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a worksheet without empty rows
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 "Matt Tyler" wrote in message om... 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").Delet e Can anyone tell me how I can force excel to only write the rows that are populated? Thanks Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a worksheet without empty rows
Debra Dalgleish has some techniques (both manual and VBA) for resetting the used
range at: http://www.contextures.com/xlfaqApp.html#Unused Matt Tyler wrote: 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").Delet e Can anyone tell me how I can force excel to only write the rows that are populated? Thanks Matt -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unwanted empty rows at worksheet bottom | Excel Discussion (Misc queries) | |||
How do I specify an end to a worksheet to eliminate empty rows? | Excel Discussion (Misc queries) | |||
empty rows at bottom of worksheet | Excel Discussion (Misc queries) | |||
How to delete empty rows in a worksheet | Excel Programming | |||
Delete empty rows in a worksheet | Excel Programming |