Resetting Worksheets for Periodic Reporting

Problem

My client has workbooks with multiple worksheets which are refreshed each month.  Therefore, at the beginning of each reporting cycle we need to clear out these worksheets.  The problem is there is no single setup for these worksheets.  They fall into three categories:

• 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.

Solutions

NOTE:  In all three of these there is an assumption that the target Worksheet is assigned to variable "wksht" and "rng" is a target Range..

Clearing all of a worksheet

This is the simplest of the three.  My preference is wksht.UsedRange.Delete because it resets the UsedRange of the worksheet.  The UsedRange of a worksheet establishes the scroll bars' understanding of the last row and column of the worksheet.

An alternative would be to use wksht.Cells.Clear or some combination from this With statement:

With wksht

    .ClearComments

    .ClearContents  ' or .ClearNotes for those still working with older workbooks.

    .ClearFormats

    .ClearHyperlinks

    .ClearOutline

End With

Once this is done, if it is important (or just plain useful) to reset the Used Range, follow what is described in Resetting worksheet UsedRange.

Clearing Rows and Columns

Everything but the headers

This is only mildly more complex.  The code now has to limit the action to a range of Rows or Columns within the worksheet, e.g. wksht.Rows(2 & ":" & ws.UsedRange.Rows.count) and wksht.Range(Columns(3), Columns(ws.UsedRange.Columns.Count)) instead of wksht 

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

Sometimes there are non-contiguous rows or columns to be deleted or cleared.  For example, resetting a roster of an unspecified number of people , then a list of contacts of unspecified length below the roster.

Warning about deleting rows and columns:  Everything shifts up or to the left, depending on if deleting rows or columns, respectively. Therefore, when deleting multiple sets of rows/column make certain they are deleted in descending numeric/alphabetic order, that is from the bottom to top or right to left of the work sheet.

To accomplish this, create a data structure then use it as an array, sort the array, and stepping through the array, delete or clear the rows/columns. (NOTE: Sorting the array is not necessary if Clearing the rows/columns, but I find it good practice because I don't want to forget and then spend time scratching my head when I've been deleting.)

Clearing Specific Areas

This is a different method altogether.  It is useful when there are formulas on the worksheets that should remain, e.g.  summary rows and columns or a worksheet has multiple sections, each summarizing from other worksheets. Below are two methods.  Both depend on using Names to identify areas of the worksheets.

• 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 Name

        For Each nm In wb.Names

            If InStr(1, nm.Name, NameElement) Then

                nm.RefersToRange.ClearContent

            End If

        Next n

    End Sub
 

• 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 Name

        For Each nm In wb.Names

            If InStr(1, nm.Name.Comment, CommentElement) Then

                nm.RefersToRange.ClearContent

            End If

        Next n

    End Sub

•