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

Hello Eonizuka,

Perhaps the following code will meet your needs. Try it in a backup copy of your workbook first, of course.

Sub removeRows()
Dim i As Long
Application.ScreenUpdating = False
For i = Sheets("Sheet1").UsedRange.Rows.Count To 1 Step -1
    If Cells(i, "B").Value <> "Group:" And _
      Right(Cells(i, "Q").Value, 1) <> "%" Then _
        Cells(i, "Q").EntireRow.Delete Shift:=xlUp
Next i
Application.ScreenUpdating = True
End Sub

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.

If Cells(i, "B").Value <> "Group:" And _
  Cells(i, "Q").Value > 1 Then _

Hope that helps!

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.

Option Explicit
Sub DeleteYellowRows()
Dim LR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 12).End(xlUp).Row
For a = LR To 1 Step -1
  If Application.WorksheetFunction.IsNumber(Cells(a, 12).Value) And Cells(a, 12).Interior.ColorIndex = 43 Then
    Rows(a).EntireRow.Delete
  End If
Next a
Application.ScreenUpdating = True
End Sub

Then run the "DeleteYellowRows" macro.

To delete the shapes, try adding this code:

Just above "Application.ScreenUpdating = False", add:

Dim sh as Shape

and just before "Application.ScreenUpdating = True" add:

For Each sh In Sheets("Sheet1").Shapes
    sh.Delete
Next sh

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

If Cells(i, "B").Value <> "Group:" And _
      Right(Cells(i, "Q").Value, 1) <> "%" Then _
        Cells(i, "Q").EntireRow.Delete Shift:=xlUp

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.