macro to delete rows with a range for values

**poole** · 03-28-2009, 12:48 PM

I need some assistance with a simple macro. Im clueless when it comes to macros.

Heres what I have:

column "e" contains the year built for homes. Some of the fields contain "9999" or "0000" or blank. I need to delete these entire rows. If the year is between 1900 and 2020 I would consider it valid and keep the row.

So I need a macro that says something like this:

if column "e" is not between 1900 and 2020 delete the entire row.

Thanks very much for your assistance!!!

**shg** · 03-28-2009, 12:50 PM

Welcome to the forum.

Please take a few minutes to read the forum rules about thread titles, and then edit yours accordingly.

**poole** · 03-28-2009, 01:03 PM

Changed title and read rules.

**VBA Noob** · 03-28-2009, 01:10 PM

Thanks for taking the time to read and comply with the rule

.

Try thus modified code from this website

http://www.rondebruin.nl/delete.htm#AutoFilter

Please Login or Register  to view this content.

VBA Noob

**Shijesh Kumar** · 03-28-2009, 01:13 PM

here u go

Please Login or Register  to view this content.

**JBeaucaire** · 03-28-2009, 01:15 PM

Give this macro a whirl:

Please Login or Register  to view this content.

**poole** · 03-28-2009, 01:19 PM

Man...you people are awesome!!! Thanks for the help!!!

**VBA Noob** · 03-28-2009, 01:20 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**JBeaucaire** · 03-28-2009, 01:20 PM

Originally Posted by Shijesh Kumar

Please Login or Register  to view this content.

Shijesh, when the goal is the deletion of rows, you have to start at the bottom and work up. If you start at the top then stuff gets missed.

If rows 11 and 12 would both fail and require deleting, deleting row 11 moves row 12 up to row 11 and then the "next" command moves to the new row 12...effectively missing the old row 12 because it's now on row 11.

For deletions, start at the bottom.

**VBA Noob** · 03-28-2009, 01:23 PM

For deletions, start at the bottom.

In general but no need with the filter method and usually faster IMO

VBA Noob

**Shijesh Kumar** · 03-28-2009, 02:14 PM

opps You are right ... since I work with Oracle Database i have never knew this limitation in EXCEL...

Thanks >> Learned something new today :-)

Please Login or Register  to view this content.

**poole** · 03-28-2009, 02:18 PM

Macro worked great, but I have one more issue. Let me know if I need to start a new thread.....new at this.

I am referencing my data on another worksheet using something like this "=data!A2" when I initially put the data in the spreadsheet...it transfers just fine into the other worksheet. After I manipulate the data (run the macro), then switch over to the worksheet that references my data, some of the cells show up like this "=data!#REF!" Its like it loses the "A2"part.

Any thoughts? Do I need to mark this thread as solved then start another?

Thanks again!

macro to delete rows with a range for values

LinkBack

Thread Tools

Rate This Thread

Display

macro to delete rows with a range for values

Re: need help with simple macro

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Re: macro to delete rows with a range for values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions