+ Reply to Thread
Results 1 to 5 of 5

Clear Content if cell in a range contain #N/A VBA

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    19

    Clear Content if cell in a range contain #N/A VBA

    Hi All,

    I am trying to write a VBA that clear contents if cells contain #N/A from a Range of cells? The Below VBA ran without error but did not clear the cells with #N/A.



    Dim arange As Range

    Range("A5:F29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A38").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Set arange = Range("A38:F62")
    For Each Cell In arange
    If IsError(arange) Then
    arange.ClearContents
    End If
    Next


    End Sub

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Clear Content if cell in a range contain #N/A VBA

    since you copy and paste values, there is no ERROR anymore, there are values #N/A. you need to look for values and if the value is found, clear the content.
    also, declare the Cell the way you declare arange, and also try no to use Cell, maybe rCell to avoid any confusion or clashes with Excel reserved words.
    Click on the star if you think I helped you

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Clear Content if cell in a range contain #N/A VBA

    Hi Alex,
    Firstly, please see forum rules and use "Code" tags around your VBA code to make it clearer to follow your post.

    Now, as for your request, please change the For-Next loop part of your code with the following:
    Please Login or Register  to view this content.
    Note: This should clear contents all cells with errors, not just the ones with "#N/A" error. If you only want to clear ones with "#N/A" errors, replace "IsError" with
    "IsNA" in the code.

    Hope that works. Cheers!
    Jewel

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Clear Content if cell in a range contain #N/A VBA

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-23-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    19

    Re: Clear Content if cell in a range contain #N/A VBA

    Thank you all. It worked wonder!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to clear cell's content?
    By NZL in forum Excel General
    Replies: 3
    Last Post: 03-09-2013, 10:04 AM
  2. Clear Cell Content Query
    By captedgar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2010, 06:25 AM
  3. Deleting cell no value by clear Content
    By pichai in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2008, 07:53 AM
  4. clear the content inside the cell
    By silverskye787 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2007, 11:11 AM
  5. Clear content of cell if has some character.
    By Krzys in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2007, 09:20 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