- The whole worksheet is replaced with a new report that is simply copied into the workbook. Since the worksheet needs to be in a specific place, relative to the other worksheets, it is simpler to copy and paste into the existing worksheet rather than use an approach of finding the right relative point to insert when copying or moving the worksheet from its original workbook to the destination.
- Specific rows or columns need to be cleared. For example, some worksheets have tables of data and each column has a title, and some of those have report headers.
- There are specific areas that need to be cleared. These have both column and row titles. On some worksheets there are multiple areas to clear.
Clearing all of a worksheet
Clearing Rows and Columns
Everything but the headers
wksht.Rows(2 & ":" & ws.UsedRange.Rows.count).Delete ' Column headers in row 1. Everything below that deleted wksht.Range(Columns(3), Columns(ws.UsedRange.Columns.Count)).Delete' Row headers in Columns A & B. Everything to the right of that deleted
Multiple Sets of Rows or Columns
Clearing Specific Areas
- Set-up: If the worksheet structure is static, i.e. the same ranges are used every month such as B2:Z24 always has hourly data for each workday of a four week period, then they can be Named using the Excel Formulas ribbon's Name Manager functions.
If the worksheet structure is more dynamic, i.e. the worksheet is for each workday of the month, these need to be established by VBA code each month the worksheet is refreshed.
- Method 1: Using the Name attribute of the Name objectEach Name can have a common element in the Name, e.g. Data1, Data2 etc or <subject>_Data. Two Name objects can have the same Name attribute only if they are scoped differently, e.g. to the workbook or to one of the worksheets. Then use the following code to clear them
Sub ClearByNameElement(wb as Workbook, NameElement as String) Dim nm As NameFor Each nm In wb.NamesIf InStr(1, nm.Name, NameElement) Thennm.RefersToRange.ClearContentEnd IfNext n
- Method 1: Using the Comment attribute of the Name objectEach Name can have a common element in the Comment, e.g. ClearEachMonth or <subject>_ClearEachMonth. There is no need to differentiate each Comment as it is with the Name attribute
This is useful if there are already Names in the workbook and it may be easier to tag them in something the user's don't see. The following code clears these areas.
Sub ClearByNameComment(wb as Workbook, CommentElement as String) Dim nm As NameFor Each nm In wb.NamesIf InStr(1, nm.Name.Comment, CommentElement) Thennm.RefersToRange.ClearContentEnd IfNext n