?
Solved

Excel Run time error 1004  Application-defined or Object-defined error

Posted on 2014-08-23
3
Medium Priority
?
2,985 Views
Last Modified: 2014-08-23
I'm getting the following error when I try to run a routine in an Excel macro:
"Excel Run time error 1004  Application-defined or Object-defined error"

The routine is listed below.  I am attempting to delete all rows/records within the used range except those that have "California" as the value in column "A". Columns "A" through "E" are the only columns in the used range and each cell within that range may or may not contain data (i.e. there are some cells or entire rows within the range that are blank or may contain garbage data).

Dim lastrow As Long, I As Long

'get last row
lastrow = Range("F" & ActiveSheet.Rows.Count).End(xlUp).Row
Do While I <= lastrow
If Cells(I, 1) <> "California" Then                                    <------ GETTING THE ERROR ON THIS LINE
Rows(I).Delete
'decrement value of lastrow since we deleted a row
lastrow = lastrow - 1
Else
'increment counter to go to next row
I = I + 1
End If
Loop
0
Comment
Question by:dbfromnewjersey
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40281087
See if this helps

Dim lastrow As Long, I As Long

'get last row
lastrow = Range("F" & ActiveSheet.Rows.Count).End(xlUp).Row
For I = lastrow to 1 Step -1
    If Cells(I, 1) <> "California" Then                                  
        Rows(I).Delete
    End If
Next

Open in new window

0
 

Author Comment

by:dbfromnewjersey
ID: 40281152
That works. Thank you very much.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40281153
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

839 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