Sheet Cleaning (Not Detergents)

Posted on 2012-08-26
Last Modified: 2012-08-27
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?
Question by:Rayne
    LVL 8

    Assisted Solution

    by:Elton Pascua
    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

    LVL 44

    Accepted Solution

    LVL 81

    Assisted Solution

    by:zorvek (Kevin Jones)
    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:



    Author Closing Comment

    You guys are awesome
    With so many options to choose from....
    Hail the Great EE!!
    LVL 8

    Expert Comment

    by:Elton Pascua
    You're welcome Rayne.

    Zorvek - thanks for that. I didn't know that limit until today - another learning experience!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now