[Last Call] Learn how to a build a cloud-first strategyRegister Now


error message when using code in excel 2007 / 2003

Posted on 2012-09-21
Medium Priority
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

rejoinder earned 2000 total points
ID: 38423776
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

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 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