Programming codex

How To Remove Page Breaks And Objects From An Excel Worksheet Using VBA

Remove Page Breaks & Objects From An Excel Worksheet Using VBA

[ad_1]

If you need to remove data from an existing worksheet, the process is straightforward, but it gets a bit trickier to remove formatting, page breaks and charts.

To clear a worksheet of data you might use code like this:

cells.clearContents

This will delete the data from the worksheet, but be sure to make a copy first as there is no undo command when using VBA, unlike regular Excel functions and tools.

If you entered data in bold type, or italics the format is retained in the cell, even if the data has been deleted.You can remove the formatting manually but of course a VBA procedure is even better.

To completely clear the worksheet, the page breaks, formatting and charts need to be removed and this article explains the VBA code that effectively resets the sheet to a blank canvas.

Removing Page Breaks And Formatting

Formatting can be reset in the same code snippet as the clear contents method using the with method. As a bonus the code shows any hidden rows and sets a standard column width.

Removing the page breaks is a standard command that is applied to the active worksheet.

with Cells

.ClearContents

.ClearFormats

.ColumnWidth = 10

.EntireRow.Hidden = False

End With

ActiveSheet.ResetAllPageBreaks

Deleting Charts From the Worksheet

To remove any charts from the sheet is a little more complicated; if there any charts on the worksheet, they form part of the objects collection and the entire collection can be deleted. If there aren’t any charts, the loop is ignored in the code.

For x = 1 To ActiveSheet.ChartObjects.Count

ActiveSheet.ChartObjects(x).Delete

Next

You might also need to set the row height to a more practical size, although if you find there’s too much to reset, it might be easier to delete the entire worksheet and start again with a new one.

Where this code might be useful is where you need a working template, for data which might be entered every week for a report, and while the data and charts might need to be removed the formatting can be retained. In this case, it would be a simple matter to fine tune the code to remove everything except the formatting.

Summary

While in most cases it might be more practical to start again with a new worksheet, there may be cases where you need to remove some of the elements of the sheet, and keep other properties intact.

[ad_2]

Source by Andy L Gibson

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on whatsapp

Leave a Reply

Your email address will not be published. Required fields are marked *

Search:

Topics

Recent Posts