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.


What is better way to clear content the entire sheet?
Who is Participating?
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:

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

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:


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

Zorvek - thanks for that. I didn't know that limit until today - another learning experience!
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.