Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Delete Filtered Rows From VBA

    I am hoping there is a way to delete filtered rows on a Excel 2003 spreadsheet from code.
    I recorded a macro to use the code to filter and delete all rows that met the criteria. Problem: the macro code references the specific rows I deleted:

    Code:
    Rows("63:63").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Delete Shift:=xlUp
    Two rows make up my headings, and I use command buttons and forms with code to run a music program.
    I have a large list (over 17,000 rows) of songs that can be "filtered" on, however I found that the functions I have in the spreadsheet slow to a crawl when working from a filtered list. My solution to that is to keep a separate "Master List". The viewing list can appear to be filtered by deleting all rows that fall outside of the criteria, for example, view only songs that end with the word "Love" (411 of them). All rows are associated with sheet music that is viewed from PDF files, and some can be played with MP3 and/or MIDI files stored on disk. I found that I could open the PDF viewer and send it the PDF file name and page number to go to. The viewing of sheet music and playing audio files is facilitated by using the SelectionChange event of the worksheet, which executes the code if a cell in 1 of 3 specific columns is clicked, columns for PDF, MP3 and MIDI.

    The filter and deletion process from code takes only a couple of seconds and the function to completely restore the viewing list (copy master list back into the viewing list) takes about 1 second.

    Thanks for helping me to solve deleting filtered data rows from VBA code.
    Jerry
    Last edited by JerryDal; 07-01-11 at 13:37. Reason: spelling, wording

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I know it's not an answer to your question but if you moved your entire table to access, you would have a lot more freedom to do things like this.

    It wouldn't take a lot to move it over, although some of your commands would need modifying, but you'd be able to query and refresh your list a lot faster.

    Aside from that I'm afraid that I can't help you with the above.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You could try writing a sub to loop through the column of the master list that you're filtering, looking for any that match. If it finds one, it copies that row over to the next available row on the sheet that will use it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Post

    Thanks for those responses. This project is a challenge for me to see if I can create an Excel application that allows me to manipulate 17,000+ rows of song titles, and be able to view sheet music and play audio files associated with some of the songs, and have reasonably fast code performance. I was able to resolve this filtering issue by inserting two dummy rows above row 3, which is the first data row, and I can then use these two rows to predict in VBA code the starting points of two delete operations.

    I'll summarize the solution before I rant on about how it works. The formulas are generated in VBA and this scenario is searching for song titles, in column A, ending in specific text of 4 characters:

    -add temporary formulas to three columns
    -formula #1 in column I: Upper(Trim(Right(A5, 5)))
    translation: display last 5 characters in cell A5 in uppercase, trim
    leading space if there is one
    -formula #2 in column J: =IF(ISERROR(FIND("LOVE",I5)),0,FIND("LOVE",I5))
    translation: If target "LOVE" is found in column I, display "1"
    otherwise display "0"
    -formula #3 in column K: =FIND(RIGHT(I5,1),")?!E")
    translation: if the value in column I ends with one of these four
    characters: ) ? ! E then display a number greater than 0,
    otherwise display #VALUE!
    -copy the 3 formulas down to the last data row
    -copy all formulas as values
    -filtering stage 1 - filter for "0" in column J, delete all rows and then
    clear column J filter
    -filtering stage 2 - filter for #VALUE! in column K, delete all rows
    -remove AutoFilter
    -clear contents of columns I, J, K
    <end of summary>

    The first filtering stage acts on a formula added to a column (J) via code. In this example, I want to view only song titles that end with the word "LOVE":
    Code:
    =IF(ISERROR(FIND("LOVE",I5)),0,FIND("LOVE",I5))
    In this example, temporary column I contains the last 5 characters of the song title, which, for those rows I want to keep, would result in one of the trimmed values of "LOVE" "LOVE)" "LOVE!" or "LOVE?". Column J will have only the values "0" or "1" and the dummy row #3 has been assigned a "0".
    The first filtered delete gets rid of all the rows with zeros in column J, starting from row #3 because of the assigned "0". The code then clears column J filter and filters on the formula in column K:
    Code:
    =FIND(RIGHT(I5,1),")?!E")
    and this VBA-created formula evaluates to: If the song title ends with one of these four characters: ) ? ! E then display a number greater than zero, otherwise display #VALUE! Previously the code filled in column K of the dummy row now at row #3 with "#VALUE!".
    All rows with #VALUE! in column K are then deleted, which removes song titles like "I'm Looking Over A Four-Leaf Clover".
    This 2-stage delete scheme allows me to include songs that may end with the target text, or just one word, but have added punctuation, such as "Chanson D' Amour (Song Of Love)".

    I use Application.ScreenUpdating=False at the start of the code and
    Application.ScreenUpdating=True at the end of of code to hide screen movement during code processing.

    The process takes about 5 seconds. Prior to this I tried using a loop to examine and delete each non-target row, but this took minutes to complete.
    RESOLVED

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Once the rows are filtered, you can use the Range.SpecialCells(xlCellTypeVisible) method to quickly get a reference to the visisble rows and delete them all in one go. You just have to be careful to exclude the column headers.

Posting Permissions

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