1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ClearContents VBA macro help

Discussion in 'VBA Macros' started by westend9876, Feb 12, 2019.

  1. westend9876

    westend9876 Member

    Messages:
    124
    I'm trying to clear the contents from cells in the following seven worksheets within my workbook. However, I get an error message: "Run-time error '13' Type mismatch". My code is below. What am I doing wrong?
    Screen Shot 2018-12-20 at 13.32.42.png

    Code (vb):

    Sub Clear_All()
    Dim aRange, bRange, cRange, dRange, eRange, fRange, gRange As Range
            Set aRange = Sheets("FHA_Stream").Range("B4, B6, B10, B13, B14, B20, B23, B24, H26").ClearContents
            Set bRange = Sheets("VA_IRRRL").Range("B4:B6, B9, B13:B14, B17, B21:B22, E9, H9").ClearContents
            Set cRange = Sheets("Full_Doc").Range("B1, B2:B5, B10:B11, B14, B18:B22, B25, B29:B33, E25, H12:H14").ClearContents
            Set dRange = Sheets("VA_IRRRL").Range("B4:B6, B9, B13:B14, B17, B21:B22, E9, H9").ClearContents
            Set eRange = Sheets("Amortization").Range("B7, H5:I7").ClearContents
            Set fRange = Sheets("Payment-Calc").Range("C3:C5").ClearContents
            Set gRange = Sheets("MSP_Inputs").Range("I1:I23, I28:I31, I33, I35, I41:I55, K24:L27, K36:L39 ").ClearContents
    aRange.ClearContents
    bRange.ClearContents
    cRange.ClearContents
    dRange.ClearContents
    eRange.ClearContents
    fRange.ClearContents
    gRange.ClearContents

    End Sub
     
    Last edited by a moderator: Feb 13, 2019
  2. Belleke

    Belleke Active Member

    Messages:
    545
    Hi,
    Try this.
    Code (vb):
    Sub Clear_All()
    Application.ScreenUpdating = False
        Sheets("FHA_Stream").Range("B4, B6, B10, B13, B14, B20, B23, B24, H26").ClearContents
        Sheets("VA_IRRRL").Range("B4:B6, B9, B13:B14, B17, B21:B22, E9, H9").ClearContents
        Sheets("Full_Doc").Range("B1, B2:B5, B10:B11, B14, B18:B22, B25, B29:B33, E25, H12:H14").ClearContents
        Sheets("VA_IRRRL").Range("B4:B6, B9, B13:B14, B17, B21:B22, E9, H9").ClearContents
        Sheets("Amortization").Range("B7, H5:I7").ClearContents
        Sheets("Payment-Calc").Range("C3:C5").ClearContents
        Sheets("MSP_Inputs").Range("I1:I23, I28:I31, I33, I35, I41:I55, K24:L27, K36:L39 ").ClearContents
    Application.ScreenUpdating = True
    End Sub
    westend9876 likes this.
  3. vletm

    vletm Excel Ninja

    Messages:
    4,793
    westend9876
    Your question was 'What am I (westend9876) doing wrong?'

    Why do You declare those ranges?
    If You're using set... with ...).ClearContents
    hmm? next You'll use those ranges again with .ClearContents?

    Test to use something like code in #2 Reply.

Share This Page