Results 1 to 8 of 8

Clear contents - no formulas

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Clear contents - no formulas

    Hello,

    I have a macro for clearing contents after copying first sheet and creating new ones. VBA code is for clearing contents, but to leave all formulas within range intact.

    Code is :

    Sub delete_cells()
    
    Dim lngCounter As Long
    Dim rConstants As Range
    
    
    For lngCounter = 2 To 13
    
      Set rConstants = Sheets(lngCounter).Range("E3:AI318").SpecialCells(xlCellTypeConstants)
          rConstants.ClearContents
    
    Next lngCounter
    End Sub
    Code works when there are actually some cells to clear, but If none (cells are clear - except formula cells) I receive error "no cells were found". How can I fix that, I want this code only to delete cells If user accidently types something in first sheet which is being copied.

    Tried this but not working :

    Sub delete_cells()
    
    Dim lngCounter As Long
    Dim rConstants As Range
    
    
    For lngCounter = 2 To 13
    
      Set rConstants = Sheets(lngCounter).Range("E3:AI318").SpecialCells(xlCellTypeConstants)
       If rConstants <> "" Then
          rConstants.ClearContents
          End If
       
    Next lngCounter
    End Sub
    Last edited by Lukael; 03-17-2014 at 11:56 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Clear cell contents on closing/opening without clearing formulas
    By CFT Brian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2013, 03:47 PM
  2. [SOLVED] How do I clear contents from a range excluding formulas and text?
    By Normezo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-23-2012, 10:36 AM
  3. excel command to clear contents except 2 formulas
    By Lynda S in forum Excel General
    Replies: 6
    Last Post: 07-14-2006, 10:15 AM
  4. [SOLVED] Clear contents but leave formulas in Excel
    By ojchippy in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 06-16-2006, 11:15 PM
  5. [SOLVED] [SOLVED] Excel should let me clear cell contents without clearing formulas
    By BJM in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-19-2005, 01:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1