Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: ClearContents not working

  1. #1
    New Member
    Join Date
    Dec 2011
    Posts
    35
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default ClearContents not working

    I am trying to run a Macro in Sheet1 which worked perfectly before. For reasons unknown to me, it is not working tonight and I get an error at the line ClearContents, RunTime Error 1004, ClearContents Method of RangeClass failed. As an aside, I am also having problems getting my computer to enter quotation marks while in the VBA editor. I have to put a space or a second character for the quote mark to show up and "I comes out as an umlaut over the I. It doesn't happen in other programs. I am stumped.


    Dim rrow As Integer
    Dim j As Integer
    Dim k As Integer
    Dim z As Integer
    Dim holding(50)
    Dim Sum As Integer
    Dim i As Integer
    Dim wrkRng As Range
    Dim MaxRng As Range
    Dim MinRng As Range
    Dim n As Integer
    Dim y As Integer






    Worksheets("Sheet1").Select
    Set wrkRng = Worksheets("Sheet1").Range("A1:B10")
    Set MaxRng = Worksheets("Sheet1").Range("A20:A29")
    Set MinRng = Sheets("Sheet1").Range("B20:B29")

    Sheets("Sheet1").Activate
    wrkRng.ClearContents


    MaxRng.ClearContents
    MinRng.ClearContents

    If I put apostrophe in front of the the three range.ClearContents statements, the code runs to the next ClearContents statement and I get the same error.

    Sheets(i).Select
    With Sheets(i)
    .Range("G3:I11").ClearContents

    .Range("J3:L11").ClearContents

    .Range("G2:G11").Value = Sheets("Sheet1").Range("A20:A29").Value
    .Range("J2:J11").Value = Sheets("Sheet1").Range("B20:B29").Value
    Last edited by SGBCleve; Nov 8th, 2017 at 12:13 AM. Reason: Additional info.

  2. #2
    New Member
    Join Date
    Dec 2011
    Posts
    35
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ClearContents not working

    In addition, a black bordered box is showing up, except it is not always in line with a cell, and cells I select are not highlighted.

  3. #3
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    2,816
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ClearContents not working

    Maybe the sheet is protected? You can use UserInterfaceOnly:=True for Sheet1.Protect option so that code can make changes. I normally do that for all worksheets in Thisworkbook's Open event.

    Select, Activate, Selection, and such are seldom needed.

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    14,063
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    6 Thread(s)

    Default Re: ClearContents not working

    You need a End With statement like this:
    Code:
    With Sheets(i)
     .Range("G3:I11").ClearContents
    
     .Range("J3:L11").ClearContents
    
     .Range("G2:G11").Value = Sheets("Sheet1").Range("A20:A29").Value
     .Range("J2:J11").Value = Sheets("Sheet1").Range("B20:B29").Value
    End With
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Dec 2011
    Posts
    35
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ClearContents not working

    It seems to have been a problem with my computer. I closed Excel and restarted it, and now it's working. Not sure why. The sheet was not protected, and the End With statement was further down, but thanks for the help
    Last edited by SGBCleve; Nov 8th, 2017 at 12:37 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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