Clear Cells in Excel Range Worksheet using VBA

Home/Excel VBA/Clear Cells in Excel Range Worksheet using VBA


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



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

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()
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()
End Sub
Clearing Entire Worksheet using Clear Method

This method will clear entire worksheet including formats.

Sub sbClearEntireSheet()
End Sub
Clearing Only Data from Worksheet using ClearContents Method

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

Sub sbClearEntireSheetOnlyData()
End Sub
  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

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.

By |June 9th, 2013|Excel VBA|26 Comments

About the Author:

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.


  1. Dave July 13, 2013 at 10:53 PM - Reply

    Please note that there is a typo in your code above; ClearContents is used for both examples.

    The first instance should read:

    The second instance is correct as:

  2. PNRao July 13, 2013 at 11:01 PM - Reply

    Thanks Dave! Corrected it!


  3. Felipe March 11, 2015 at 11:54 AM - Reply

    I want to erase some of the contents but not all from cells. For example in every cell I have several qualifiers with an specific value, something like /qualifier1=value2 /qualifier2=value2 etc. I just want to erase all qualifiers but number 1. Is this possible?

    Thank you in advance!

    • PNRao March 21, 2015 at 2:38 PM - Reply

      Hi Felipe,
      There is no direct method/finction to do this task. You can write a formula or VBA procedure to do this.


  4. Dushyant July 8, 2015 at 11:12 AM - Reply

    hello sir

    Can we clear excel data of two excel sheet of specific cell’s in one code

    e.g. i have two excel sheet in my workbook A and B and i wont clear data of specific cell using range option (A3:B10) for Sheet A and (C2:D10) for sheet B, i want this operation in One code only
    can you please help me


    Dushyant Padhya

    • PNRao July 8, 2015 at 5:30 PM - Reply

      If you want clear multiple ranges from a sheet with one single clear statements, you can use the below method:

      Sheets("SheetA").Range("A3:B10", "C2:D10").Clear

      If I am correct, you want to put tow statements in one single statement:
      Below is the code to clear the two ranges:


      You can use “:” to concatenate the VBA statements

      Sheets("SheetA").Range("A3:B10").Clear: Sheets("SheetB").Range("C2:D10").Clear

      Hope this hellps!

  5. james August 7, 2015 at 7:43 PM - Reply

    I have a list where some cell values begin with a letter and some with a number. How can I use the clear function to clear cells beginning with a letter but leave those beginning with a number? All cells contain both letters and number, but I want to clear those where the letter is the first character. also some are uppercase and some lowercase (not sure if this matters)


    • PNRao August 7, 2015 at 7:57 PM - Reply

      Hi James,

      The below macro checks for the first character of each cell in a given range, and clears if it is non-numeric:

      Sub sbClearCellsIFCritera()
      Set Rng = Range("F1:F20") ' change this range as per your requirement
      For Each cell In Rng.Cells
      If IsNumeric(Left(cell, 1)) = False Then
      End If
      End Sub


  6. ravindra August 22, 2015 at 10:40 PM - Reply

    I want to get the data from one workbook to another workbook by using VBA coding. So could you please help me.

    • PNRao August 23, 2015 at 2:10 AM - Reply

      Hi Ravindra,
      You can use the Copy command as shown below:

      Workbooks("Book1").Sheets("Sheet1").Range("A1:B10").Copy _


  7. Brad Bouchaud November 6, 2015 at 7:47 AM - Reply

    Hi and thanks in advance.
    I have a List of 3 items per row in a Worksheet with 30 such rows and a button besides each to run a Macro to clear the contents when required.
    Other macros perform functions on the data in the lists.
    Unfortunately I am using the .ActiveCell which doesn’t seem to detect I am in the Cell with the button but is uses the last cell I was in, any ideas on how I can clear the contents of the 3 cells beside the buttons without writing 30 different macros?

  8. Kyle Minnett March 2, 2016 at 1:34 AM - Reply

    If I want to clear a variable range of cells based on a specific input how would I do this?

    for example lets say i am running a code that fills cells e5:e10 based on an input variable that i have chosen. then i decide that i want to change that input variable and by changing it my data range runs from cell e5:e9. however because i just ran a calculation that created a range from e5:e10 the value in cell e10 is still present with the new range ( i want to the contents in cell e10 to be cleared)….i hope this wasn’t too confusing.

  9. Venkata Naidu M May 31, 2016 at 12:38 PM - Reply

    I want to clear the data without formulas from active worksheet

    Please help…

    • PNRao June 4, 2016 at 9:44 PM - Reply

      Hi Venkat, You can use Activesheet.Cells.Clear Method to clear the entire sheet (Activesheet).


  10. Anas Ahmad July 19, 2016 at 12:34 PM - Reply

    Hello Everybody,

    I want clear particular range of data from cells.

    For example: Cells which have only zero and the value above 5000 from the whole sheet.

    Can u suggest me how to give coding.

    • paige January 11, 2017 at 10:49 AM - Reply

      Hi Anas,
      I have the exact same query – it’s very tricky.
      did you end up finding a solution?

      Thank you PNRao for everything thus far!

  11. ASAD August 12, 2016 at 5:38 PM - Reply

    how to use check box to clear and unclear cell

    • PNRao August 14, 2016 at 11:35 PM - Reply

      We can use .Clear Method to clear the Cells but, we can do not have the method to undo /unclear the cleared cells:
      The below Code will clear when you select the check box.

      Private Sub CheckBox1_Click()
      If CheckBox1.Value = True Then
      End If
      End Sub


  12. Pranav Roy October 28, 2016 at 10:32 PM - Reply

    I’m new to macro programming
    How to write a macro with relative refrences which can clear/clear contents after a particular cell. That is if a cell is chosen and macro is started it should delete the values or formats for the given no ( say 12) cells .May be row or columnwise.

  13. Donna November 2, 2016 at 6:40 PM - Reply

    I have one workbook with several sheets, (more than 200 sheets) and I would like to clear contents within specific cells (same on all sheets) in workbook. How can I do that each sheet is named with last 4 digits of each VIN.
    What code would I use?

  14. srinivas January 7, 2017 at 5:11 PM - Reply

    I want to this macro code
    one excel sheet first cell to six cells type “p” letter then seventh cell automatic display “wp”
    please tell me this vba code

  15. Himani January 10, 2017 at 4:12 PM - Reply

    Is there any way to clear data of selective rows from multiple sheets keeping the formatting and formula same??


  16. April January 25, 2017 at 5:34 PM - Reply

    I want to clear the values that are returned in a range of cells but, I want to keep the formula that was entered in the cells. Is there a way to do that?

  17. Nasiba February 17, 2017 at 12:56 AM - Reply


    How can I clear content of multiple tabs? For example I need to clear Range(“A:N”) columns from 5 different worksheets

  18. Yaried May 29, 2017 at 2:27 AM - Reply

    hi every body I do have a excel template, and I do have a program for clearing data but I also want to add an option whether to clean the data, ie yes no option if the user press the no button the data will not be cleared can u tell me a command for that case. could you pleas send me a mail
    thanks for your cooperation

  19. Chittaranjan June 20, 2017 at 4:52 PM - Reply

    Hello All,

    Need help on one thing, Whenever i open Excel which containing macro an error occurs as “Security Warning Macro have been Disable. Enable Content” . After clicking on Enable content button the excel open in good manner.
    Now I have to disable any cell value or change color to while, let say cell F5 having text “Best of Luck” which is in black color. I record the macro as to change it to white color/it should be disabled. OK.

    My Question is, whenever we click on Enable content button, the text color “Best of Luck” of cell F5 should be change to white/ disappear.

    So How to do, need help.

Leave A Comment

Related pages

excel template timelinesample project plan template excelmicrosoft excel estimate templatehow to use pivot table in excel 2010how to unhide on excelexcel f7vbs output to filemessagebox vbaexcel vba matchms access find record vbadml in sql serverwhat is iserror in excelvba inputboxcombobox vba codeexcel macro for vlookupmerging excel columnsexcel replace duplicates with blanksvba sample projectscreating dynamic charts in excelhow to change cell width in excelvba excel stringprotect excel sheet from editingadodb connection stringgantt chart excel 2003 templateexcel implementation plan templatestacked bar chart excel 2013learn vba for exceldim conn as new adodb connectionexample of vlookup functionunlock excel spreadsheetvba sortingvbinformation vbalookup excel examplesyes no msgbox vbahow to use concatenate function in excel 2010conference agenda template excelgantt project planner excel templatemultiple conditions in excelwhat are the ddl commandssystem date in vbapaste in vbaproject management excel templatesadvanced macros in exceldeleting blank rows in exceliferror excelexcel vba stringtrim function in excel vbaprotect cells excel 2007excel vba range cellshow to change the width of a column in excelschedule template for excelexcel col_index_numexcel if isna vlookupsort formula in excelstacked column chart excel 2010how to deduplicate in excelobject oriented programming in vbaexcel iferror formulavba copy pasteexcel multiple criteriavba excel saveasshortcuts keys for excelformula of countif in excelvb adomacro to unprotect worksheetunlock excel file for editingexcel vba range resizevba vbyesnoscheduling template exceldo while loop vba excelexcel protect with passwordppt project plan templatesql dmlunlock spreadsheetvbscript write to filevba for next stepuserform hideexcel copy cell contentsexcel vba copyfromrecordsetinteractive dashboard excelactivesheet paste