+ Reply to Thread
Results 1 to 7 of 7

Deleting rows with non-percentage values

  1. #1
    Forum Contributor
    Join Date
    03-18-2009
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2003
    Posts
    104

    Deleting rows with non-percentage values

    What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.

    What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....
    Attached Files Attached Files
    Last edited by eonizuka; 06-10-2009 at 12:23 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,854

    Re: Deleting rows with non-percentage values

    Hello Eonizuka,

    Perhaps the following code will meet your needs. Try it in a backup copy of your workbook first, of course.
    Please Login or Register  to view this content.
    This code will delete any row in which:
    1. Column B of that row does not contain the exact text "Group:"
    2. The last character in Column Q of that row is not "%"

    Your percentages are currently formatted as text. If they are converted to numbers using the percentage format, this code would need to change since numbers formatted as percent don't actually have a % sign in the value of the cell (it's only shown that way). If your whole number values (non-percents) in column Q will always be greater than 1, you could use that as the conditional test, e.g.
    Please Login or Register  to view this content.
    Hope that helps!

  3. #3
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Deleting rows with non-percentage values

    eonizuka,

    The macro will delete rows where column L is Numeric, and has a Yellow Interior Color Index (tested on your attached workbook).


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Adding the Macro
    1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Press the keys ALT+Q to exit the Editor, and return to Excel.

    Please Login or Register  to view this content.

    Then run the "DeleteYellowRows" macro.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  4. #4
    Forum Contributor
    Join Date
    03-18-2009
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2003
    Posts
    104

    Re: Deleting rows with non-percentage values

    That was excellent both of you, thank you very much. I know VBA isn't magic, but it just seems like it when it works as intended sometimes. One more thing I'd like the code to do is to delete those remaining blue objects(pictures).

  5. #5
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,854

    Re: Deleting rows with non-percentage values

    To delete the shapes, try adding this code:

    Just above "Application.ScreenUpdating = False", add:
    Please Login or Register  to view this content.
    and just before "Application.ScreenUpdating = True" add:
    Please Login or Register  to view this content.
    This works in Excel 2007, but I'm not positive if it's accurate for Excel 2003 (and I'm not near a PC with 2003 installed at the moment).

  6. #6
    Forum Contributor
    Join Date
    03-18-2009
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2003
    Posts
    104

    Re: Deleting rows with non-percentage values

    One last thing Paul, would you mind walking me through the logic you used in your If statement?

  7. #7
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,854

    Re: Deleting rows with non-percentage values

    Please Login or Register  to view this content.
    The value of variable i is looped from the last used row (585, for example) to row 1, meaning it's going backwards. When deleting rows it's best to work from bottom to top, otherwise you'll end up skipping rows.

    So in my IF statement,

    If B585 does not equal "Group:" AND the last character in Q585 is not a percent sign, then delete that entire row.

    If either B585 contained "Group:" or Q585 had a "%" sign as the last character, the row would not be deleted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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