• 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...

  • 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

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:
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:
Hi,
Try this.
Code:
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
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.
 
Back
Top