PDA

View Full Version : Solved: Fully clearing the used range of a worksheet



IRish3538
07-11-2012, 07:19 AM
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
07-11-2012, 07:27 AM
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
07-11-2012, 07:33 AM
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
07-11-2012, 07:38 AM
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
07-11-2012, 07:38 AM
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:

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

snb
07-11-2012, 08:31 AM
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':
Sub tst()
Application.DisplayAlerts = False
Sheets("data").Name = "data1"
Sheets.Add.Name = "data"
Sheets("Data1").Delete
End Sub

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

sheets(1).cell.delete
or
sheets(1).rows.delete
or
sheets(1).columns.delete

IRish3538
07-11-2012, 08:53 AM
"sheets(1).rows.delete" worked perfectly! I have no idea how that evaded me. Thanks!!!!