Deleting Rows Excel 2010

**extrapulp** · 10-26-2010, 11:38 AM

I would like to have some code that will go through the entire sheet and delete rows based on any data in Col I. If Col I is blank then do nothing.

I have searched the forum and read the threads on deleting rows found here http://www.xtremevbtalk.com/showthread.php?t=300757

I tried the method in post #5. I am getting an error "Run-time error '1004':
A range is required.Select at least one cell within a range and try again."
This occurs at the line ".AutoFilter field:=9, Criterial:="<>" & sTOFIND" in the code below.

Please Login or Register  to view this content.

I have read all the posts I could find searching on 'delete rows' and can not find anything that helps me to get this working.

The code above is copied from the above mentioned forum post. I changed so it will look at column I (or 9) I believe... I could be wrong there. I also changed the ILastRow line.

I am using Office 2011 for Mac. Not sure if that makes any difference or not.

Any ideas?

Thanks

**Leith Ross** · 10-26-2010, 12:23 PM

Hello extrapulp,

Your profile list several different versions of Excel and both PC and Mac versions. Which version of Excel and which machine does this code apply to?

**extrapulp** · 10-26-2010, 12:25 PM

Leith,

This is for Excel 2011 on a Mac.

Thanks

**Leith Ross** · 10-26-2010, 12:35 PM

Hello extrapulp,

That is a key piece of information that you should include in the thread title. I know that Apple stop supporting VBA after the release of Office 2008. All macros had to be written in AppleScript. Mac 2011 has brought back VBA, but not being a Mac owner, I can not evaluate the code you have provided for errors. We do have Mac user members. Hopefully, one of them can help you out.

**extrapulp** · 10-26-2010, 12:54 PM

Leith,

I didn't think to put it in the title of the thread, just put it in the body.

I will open the spreadsheet in Excel 2010 on PC and see if I still have the problem. If I do I will change the thread title to include the version and post back.

**extrapulp** · 10-26-2010, 01:01 PM

I tried the exact same code in Excel 2010. I get the following error on the same line.
"Run-time error '1004':
AutoFilter method of Range class failed"

I also changed the title of the thread to include the excel version.

Thanks

**extrapulp** · 10-27-2010, 08:51 AM

I was able to get this done by using a for loop. I could never get the AutoFilter to work.

Here is the loop I used.

Please Login or Register  to view this content.

I don't know if this is the most efficient way or not. I ended up needing 4 different loops because of different data situations. With all 4 loops it never took more than 3 seconds to go through 5000-6000 rows. I had 30+ files to run this on.