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

crunchie

New Member
Joined
Mar 10, 2011
Messages
8
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;

Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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. (y)
 
Upvote 0
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!!!!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top