+ Reply to Thread
Results 1 to 14 of 14

Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

  1. #1
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Hi!

    Could anyone help with an advice concerning creating a VBA including 2 steps:

    1)Filtering data by criteria "begins with";

    2)Delete row if cell value = 0

    See the file attached: Filtering-VBA.xls

    Thanks in advance!
    Last edited by Arty_1; 02-12-2015 at 02:24 AM.
    Regards

    Arty

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Array loop filter

    Giver A Go
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Something's wrong - not filtring, deleting rows neither.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Something's wrong - not filtring, deleting rows neither
    Must be,because I would give you a code that doesn't work

    See attached.

    Filtering-VBA.xls

  5. #5
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Nothing... unfortunately.

    I'm using XL 2003 at the moment. Could that be the reason of the issue?

  6. #6
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    I tried it on XL 2007,- it's not working.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Just move the
    Please Login or Register  to view this content.
    down.


    Please Login or Register  to view this content.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Thanks for the rep.

    The code works for me. Slightly improved ...

    Please Login or Register  to view this content.

    See the updated example.

    Regards, TMS
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    In the file you're updated:

    All the codes A; BB; CCC; DDDD; EEEE; FFFF etc. are shown as a result meanwhile

    the constraint/criteria is to filter and show only the codes which begins with A; CCC; EEEE; GG; J; LLL

    Please Login or Register  to view this content.
    and then to delete the 0 values by the priciple:

    delete row if b=c=0

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    That's what the code does, isn't it?. It filters for each element in the array and for each visible range, deletes rows where b=c=0 (in fact, b+c=0)

    After filtering and deleting rows for each element, the filter mode is cleared so you see the all the remaining rows.


    Regards, TMS

  11. #11
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    Nope. Some rows are deleted but still as a result it shows codes from A until M, furthermore values 0 are not deleted.

    P.s. rows must be deleted by principle b=c=0 not b+c=0

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    P.s. rows must be deleted by principle b=c=0 not b+c=0
    OK, that's easy enough to fix. However, with your test data, it's not a problem. And the zero values remaining do not begin with the characters in your list/array.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Need a VBA for filtering and deleting rows. Easy task 4U + REP guaranteed!

    I think a different approach will be required. Will look at it when I get a chance.

  14. #14
    Forum Contributor
    Join Date
    05-01-2014
    Location
    RIX
    MS-Off Ver
    Excel 2003; 2007
    Posts
    114

    Need a VBA for filtering data by criteria "begins with"

    I've simplified the task, consequently only filtering the data by criteria "begins with" should be provided.

    See the file attached: Filtering-VBA_simplified.xls

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filtering and deleting unwanted rows
    By joshag in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2014, 01:59 PM
  2. Filtering / Deleting Rows with Defined Prefix ?!
    By eLearner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2011, 05:06 AM
  3. Deleting rows using complex(custom) filtering
    By djreklame in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2011, 01:16 PM
  4. Deleting records after advanced filtering
    By lbflanagan1 in forum Excel General
    Replies: 3
    Last Post: 05-23-2010, 07:08 AM
  5. [SOLVED] deleting vs. filtering . . . HELP!
    By Wayne Knazek in forum Excel General
    Replies: 10
    Last Post: 07-25-2006, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1