?
Solved

Sheet Cleaning (Not Detergents)

Posted on 2012-08-26
5
Medium Priority
?
701 Views
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.

UsedRange.clearcontents
 or
 currentregion.clearcontents?

What is better way to clear content the entire sheet?
0
Comment
Question by:Rayne
5 Comments
 
LVL 8

Assisted Solution

by:Elton Pascua
Elton Pascua earned 480 total points
ID: 38335030
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
 
LVL 46

Accepted Solution

by:
aikimark earned 1040 total points
ID: 38335039
activesheet.cells.clearcontents
sheets(2).cells.clearcontents
worksheets("sheetname").clearcontents
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 480 total points
ID: 38335080
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
 

Author Closing Comment

by:Rayne
ID: 38335212
You guys are awesome
With so many options to choose from....
Hail the Great EE!!
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38335944
You're welcome Rayne.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 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