Description:

Most of the times we clear the data from a cells or a range and re-enter to do some calculations. For examples we may have some template to enter data and calculate the tax. We may want to do this for all the employees of an organization. In this case we need to Clear data Excel from a Range in Worksheet using VBA before entering the data for each employee

Clear Cells in Excel of a range or Worksheet using VBA- Solution(s):

Copy Data from One Range to Another in Excel VBAWe can clear Cells or a Range using Clear Method OR ClearContents Method of a Range or Cell. Clear will Clear the data and Formats of the given Range or Cells. And ClearContents will clear only the data, will not clear any formats.

Clear Cells Range data in Excel Worksheet using VBA – An Example

The following examples will show you how clear the data of Cells, Range or entire worksheet using Clear and ClearContents Methods.

Clearing a Cells/Range using Clear Method

This method will clear the range of cells including Formats:

Sub sbClearCells()
Range("A1:C10").Clear
End Sub
Clearing Only Data of a Range using ClearContents Method

This method will clear only clear the content or data of the range not formats (Formats remain same)

Sub sbClearCellsOnlyData()
Range("A1:C10").ClearContents
End Sub
Clearing Entire Worksheet using Clear Method

This method will clear entire worksheet including formats.

Sub sbClearEntireSheet()
Sheets("SheetName").Cells.Clear
End Sub
Clearing Only Data from Worksheet using ClearContents Method

This method will clear only data of worksheet, not formats.

Sub sbClearEntireSheetOnlyData()
Sheets("SheetName").Cells.ClearContents
End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some data in Sheet1 at A1:C10
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to run it
Conclusion:

Both Clear and ClearContents are useful based on your requirement. If you want to Clear only the Content, use ClearContent method. If you want Clear everything (Content and Formats), use Clear method.

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: June 9th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.