Sheet Cleaning (Not Detergents)

Hello Experts,

I am dealing with a unusual situation. I have a sheet that might data in some corner of the sheets like some data on A1, some on B1:B100 and random cell ranges. what is the ultimate way to clean the worksheet sheet1 of this mess.

I have aprocess module that has to work on the sheet but before it works, I need to cleanse sheet1 of this random data mess.

UsedRange.clearcontents
 or
 currentregion.clearcontents?

What is better way to clear content the entire sheet?
RayneAsked:
Who is Participating?
 
aikimarkCommented:
activesheet.cells.clearcontents
sheets(2).cells.clearcontents
worksheets("sheetname").clearcontents
0
 
Elton PascuaCommented:
Here's another approach. You can add more special cells (separated by comma) depending on what you're clearing.

Here's a list of constants: http://www.ozgrid.com/VBA/special-cells.htm

Sub ClearContents()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    On Error Resume Next
    ws.Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).SpecialCells(xlCellTypeConstants).ClearContents
    On Error Goto 0

End Sub

Open in new window

0
 
zorvek (Kevin Jones)ConsultantCommented:
SpecialCells is limited in the number of discontinuous ranges it can handle.

Do you want a completely blank worksheet?

If so, then delete all the rows:

ThisWorkbook.Sheets("Sheet1").Rows.Delete

Kevin
0
 
RayneAuthor Commented:
You guys are awesome
With so many options to choose from....
Hail the Great EE!!
0
 
Elton PascuaCommented:
You're welcome Rayne.

Zorvek - thanks for that. I didn't know that limit until today - another learning experience!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.