error message when using code in excel 2007 / 2003

Posted on 2012-09-21
Last Modified: 2012-09-22
The code below works a treat in excel 2010, however when I tried this in excel 2007 or 2003, I get an error message on "  .Resize(, 13).ClearContents"

Please can you help.

Private Sub CommandButton3_Click()
Dim Password

Password = "abc"
If InputBox("Please Enter Password") = Password Then

Application.ScreenUpdating = False

    With Sheets("Low Volume").[B8].CurrentRegion
        .AutoFilter Field:=13, Criteria1:="<>"
        With .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Archive").Cells(Rows.Count, 2).End(xlUp).Offset(1)
            .Resize(, 13).ClearContents
            .Resize(, 1).Offset(, 16).ClearContents
        End With
    End With

Application.ScreenUpdating = True

End If
End Sub
Question by:Kiwi-123
    LVL 14

    Accepted Solution

    Using Office 2007, I encountered no errors.  The macro ran - I was prompted with a password box.  I typed abc, hit enter then the script copied the cells to the archive page, and then cleared the cells from the Low Volume tab.  No error message or problems detected on my end.
    Did you have a screen shot showing any error messages at the point of failure?

    Author Closing Comment

    Thanks for your help, I will get a screenshot of the error and re-post.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now