Deleting rows with non-percentage values

[eonizuka]

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....

[Paul]

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!

[stanleydgromjr]

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.

[eonizuka]

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).

[Paul]

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).

[eonizuka]

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

[Paul]

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.