Solved: Fully clearing the used range of a worksheet

[IRish3538]

Hey guys,

I have a worksheet that I insert rows into, then later i wipe the data and formatting using "sheet1.UsedRange.Clear". The workbook is used over and over again and after a few weeks the file becomes very large even though the worksheet is blank. I'm obviously not clearing it out correctly. Any idea on how i can fully "clear" this worksheet? (without deleting it and creating a new one). Thanks for any help you can provide!

[Simon Lloyd]

Even though you have cleared the cells it is STILL the used range, the only way to prevent it is to delete them and save!

Why don't you just create an xlt (template) then each time they can fill whatever in but the template never changes or gets bigger.

See the attached.

[IRish3538]

It's part of a much larger workbook. So I'm kind of stuck with it. I might just try and code something in there to delete and addback that worksheet. Thanks for the quick reply!

[Simon Lloyd]

Ive added a template to the post above, however, if you keep a hidden copy of that sheet and then onclose delete the used one and copy the hidden one and rename it you should be good

[IRish3538]

I actually found this article on the site that helps it along in case anyone else searches this post:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

The only difference is that this is for older versions of office with 65k rows.. for newer versions, amend the end of the code to:

[VBA] .Range(Cells(1, LastCol + 1).Address & ":XFD1048576").Delete
.Range(Cells(LastRow + 1, 1).Address & ":XFD1048576").Delete[/VBA]

[snb]

I'd prefer you earlier idea: removing the sheet and adding a 'fresh' one:
Suppose the sheet you want to remove has the name 'data':
[vba]Sub tst()
Application.DisplayAlerts = False
Sheets("data").Name = "data1"
Sheets.Add.Name = "data"
Sheets("Data1").Delete
End Sub[/vba]

If you want to remove everything in the existing sheet, instead of the code you found I would choose 1 of these three equivalents:

[vba]sheets(1).cell.delete[/vba]
or
[vba]sheets(1).rows.delete[/vba]
or
[vba]sheets(1).columns.delete[/vba]

[IRish3538]

"sheets(1).rows.delete" worked perfectly! I have no idea how that evaded me. Thanks!!!!