Initialize the worksheet by cleaning up all the previous works on a worksheet

This page collects the method and properties to clean up the working worksheet. You need to get the working worksheet to be initialized by removing the previous work, all or a part. If you want to remove all the entries, it is simpler to delete the working worksheet and generate a new worksheet. You can use some of the methods and properties to clean up partially.

1. Clean up methods and properties to put in the condition appropriate to the start of a worksheet operation

  1. Clean up typed-in entry to the cells 
    					Activesheet.Cells.Clear
  2. No borders 
    					Activesheet.Cells.Borders.LineStyle = xlNone
  3. No colors in the cells 
    					Activesheet.Cells.Interior.Color = RGB(255, 255, 255)
  4. Normal Font 
    					Activesheet.Cells.Font.Bold = False
  5. Font color back to Black color 
    					Activesheet.Cells.Font.Color = RGB(0, 0, 0)
  6. Unmerge cells 
    					Activesheet.Cells.MergeCells = False
  7. Unwrap text 
    					Activesheet.Cells.WrapText = False
  8. Unhide the hidden rows and columns 
    					Activesheet.Rows.Hidden = False
					Activesheet.Columns.Hidden = False
  9. Remove the cell validation 
    					Activesheet.Cells.Validation.Delete
  10. Remove the comments 
    					Activesheet.Cells.ClearComments
  11. Remove the Hyperlinks
    					Activesheet.Hyperlinks.Delete
  12. Remove the shapes and charts 
    					Activesheet.Shapes.SelectAll: Selection.Delete
  13. Cell size back to normal 
    					Activesheet.Columns.ColumnWidth = 8.43
					Activesheet.Rows.RowHeight = 15

2. Complete Code

Sub ClearForm_MMT(wshtName)
    Set ws = ActiveWorkbook.Worksheets(wshtName)
    With ws
        .Cells.Clear
        .Cells.Borders.LineStyle = xlNone
        .Cells.Interior.Color = RGB(255, 255, 255)
        .Cells.Font.Bold = False
        .Cells.Font.Color = RGB(0, 0, 0)
        .Cells.MergeCells = False
        .Cells.WrapText = False
        .Rows.Hidden = False
        .Columns.Hidden = False
        .Cells.Validation.Delete
        .Cells.ClearComments
        .Hyperlinks.Delete
        .Shapes.SelectAll: Selection.Delete
        .Columns.ColumnWidth = 8.43
        .Rows.RowHeight = 15
    End With
End Sub
Dated on: 29-November-2019

Please fill all the fields.

Feed Back Information
Your name
Your Email
Web page in question
Subject

Write Your Message