Solved

VBA to clear data from worksheets

Posted on 2014-10-10
6
167 Views
Last Modified: 2014-10-10
I have a file that has over 30 different worksheets. Some of these have data I need to keep but the majority have data that needs to be cleared so that we can start collecting the current data and post on the sheets.

Could an expert provide me with VBA code that will clear data from all the sheets in the workbook except for those that I need to keep. There are currently 6 sheets that I need to keep so we can call them Sheet1, Sheet2 etc etc up to Sheet6

Many thanks
0
Comment
Question by:Jagwarman
6 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
Comment Utility
Hi

You could use this macro and call it for each sheet you want

Sub DeleteSheetContetn()
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
End Sub

Open in new window

HTH
Ivo Stoykov
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Define "clear"? Do you want to keep headers (and if so, which rows), page setup? Are there formulas already set up?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
Here's a simple example of completely clearing the sheets:

Sub ClearSheets()
Dim wks as Worksheet
for each wks in activeworkbook.worksheets
select case LCase$(wks.name)
' note the sheet names should be listed in lower case!
Case "sheet1", "sheet2", "sheet3", "sheet4","sheet5","sheet6"
' do nothing
Case Else
wks.Usedrange.Clear
End Select
Next
End Sub

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Jagwarman
Comment Utility
Hi Phillip

in this case I need to clear the sheets completely headers and all

Regards
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Then both Rory's and Ivo's solutions both work.
0
 

Author Comment

by:Jagwarman
Comment Utility
As I need to clear so many sheets Rory's is the better solution for me, but thank you all for your input
0

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

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

771 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

11 Experts available now in Live!

Get 1:1 Help Now