If that you must take away knowledge from an present worksheet, the method is simple, however it will get a bit trickier to take away formatting, web page breaks and charts.
To clear a worksheet of knowledge you may use code like this:
cells.clearContents
This will delete the info from the worksheet, however be sure you make a replica first as there isn’t any undo command when utilizing VBA, in contrast to common Excel features and instruments.
If you entered knowledge in daring sort, or italics the format is retained within the cell, even when the info has been deleted.You can take away the formatting manually however in fact a VBA process is even higher.
To utterly clear the worksheet, the web page breaks, formatting and charts have to be eliminated and this text explains the VBA code that successfully resets the sheet to a clean canvas.
Removing Page Breaks And Formatting
Formatting could be reset in the identical code snippet because the clear contents technique utilizing the with technique. As a bonus the code exhibits any hidden rows and units a normal column width.
Removing the web page breaks is a regular command that’s utilized to the lively worksheet.
with Cells
.ClearContents
.ClearFormats
.ColumnWidth = 10
.EntireRow.Hidden = False
End With
ActiveSheet.ResetAllPageBreaks
Deleting Charts From the Worksheet
To take away any charts from the sheet is a bit more difficult; if there any charts on the worksheet, they type a part of the objects assortment and the complete assortment might be deleted. If there are not any charts, the loop is ignored within the code.
For x = M To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(x).Delete
Next
You may additionally have to set the row peak to a extra sensible measurement, though in case you discover there’s an excessive amount of to reset, it could be simpler to delete the complete worksheet and begin once more with a brand new one.
Where this code may be helpful is the place you want a working template, for knowledge which may be entered each week for a report, and whereas the info and charts may must be eliminated the formatting could be retained. In this case, it will be a easy matter to effective tune the code to take away every thing besides the formatting.
Summary
While normally it may be extra sensible to start out once more with a brand new worksheet, there could also be instances the place you’ll want to take away a few of the parts of the sheet, and maintain different properties intact.
