How to fix : vba runtime error 1004 application defined or object defined error

[crunchie]

Hi,

I have been working on a set of data in excel for the past few days now. I am using a custom filter to get the data i want and then paste that data into a new worksheet. It was working fine untill today and now i cannot even select a row of cells without the error turning up.

Here is the code i am trying to use;

Sub RevisedResults()
 
 
    Sheets("Test_Formatting").Select
 
    Selection.AutoFilter Field:=4, Criteria1:="<>*car*", Operator:=xlAnd _
        , Criteria2:="<>*plane*"
 
 
    Selection.AutoFilter Field:=5, Criteria1:="no"
 
 
 
 
    [B][U]Range("A1").Select[/U][/B]
    Columns("A").ColumnWidth = 20
 
    Range("B1").Select
    Columns("B").ColumnWidth = 37
 
    Range("C1").Select
    Columns("C").ColumnWidth = 20
 
    Range("D1").Select
    Columns("D").ColumnWidth = 125
 
    Range("E1").Select
    Columns("E").ColumnWidth = 13
 
    Range("F1").Select
    Columns("F").ColumnWidth = 13
 
    Range("G1").Select
    Columns("G").ColumnWidth = 13
    Range("H1").Select
    Columns("H").ColumnWidth = 19
 
    Range("I1").Select
    Columns("I").ColumnWidth = 15
 
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
 
    Range("A2:I2").Select
    Range("A2:I2").Value = "'======"
 
    Range("A1:I1").Select
    Selection.AutoFilter

I can execute the search no problem but once i try to exceute the code in bold above i.e. "Range("A1").Select" i get the "1004" error.

I have tried closing and opening excel but this hasnt worked.

I have been copying and pasting this data a few times so that might be part of the problem. The data i am trying to use is roughly 150 rows in size.

Any help would be greatly appreciated.


Regards,
Crunchie





End Sub

 

[Von Pookie]

Well, you could just take out the Select statements entirely. You don't have to select a cell in the column to change the column width. Actually, you should be able to rewrite this entire macro to not have to select anything.

Sub RevisedResults()
 
 
    Sheets("Test_Formatting").Select
 
    Selection.AutoFilter Field:=4, Criteria1:="<>*car*", Operator:=xlAnd _
        , Criteria2:="<>*plane*"
 
 
    Selection.AutoFilter Field:=5, Criteria1:="no"
 
 
    Columns("A").ColumnWidth = 20
    Columns("B").ColumnWidth = 37
    Columns("C").ColumnWidth = 20
    Columns("D").ColumnWidth = 125
    Columns("E").ColumnWidth = 13
    Columns("F").ColumnWidth = 13
    Columns("G").ColumnWidth = 13
    Columns("H").ColumnWidth = 19
    Columns("I").ColumnWidth = 15
 
    Rows("2:2").Insert Shift:=xlDown
 
    Range("A2:I2").Value = "'======"
 
    Range("A1:I1").AutoFilter

 

[crunchie]

Hi Pookie,

Thanks for looking at my post. I have removed all the "Range" calls from the code but left the "Rows("2:2").Insert Shift:=xlDown" command. Once i try to execute this command the 1004 error pops up again. Would there be any reason for this?

Also, i opened a new excel program and copied the code+data to a new sheet and everything worked fine. Could it be a problem with the original workseet?

If ive made anything unclear let me know.

Thanks in advance

 

[Von Pookie]

It's possible it could be something with the sheet. For example, if you add a lot of rows, the program could eventually consider the end of your "used" range to be the last row on the sheet--even if it is blank--so when you try to add a row it has nowhere to go.

Sometimes it just happens and starting with a fresh workbook just makes things easier

 

[crunchie]

Hi Pookie,

Opening a new workbook seems to have done the trick so hopefully i wont have any more problems. Thank you very much for the help.

 

[DemDigidiz]

It's possible it could be something with the sheet. For example, if you add a lot of rows, the program could eventually consider the end of your "used" range to be the last row on the sheet--even if it is blank--so when you try to add a row it has nowhere to go.

Sometimes it just happens and starting with a fresh workbook just makes things easier

WOW!
Von Pookie this has to be shared!!
The actual problem is that there is too many blank rows/columns!!

I read this reply, deleted a bunch of empty columns I had, and BAM! the code works. I WOULD HAVE NEVER IMAGINED THIS COULD BE THE PROBLEM!
You're a genius!

Many many thanks!!!!