• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7035
  • Last Modified:

Excel VBA Code to Delete Row(s) based on Condition/Criteria

Hi Excel gurus,

I need a specific VBA/Macro to delete rows based on a simple criteria of values in between -5% to 5%.  I.E. a row with -4%, 4%, 0, or N/A will be deleted.

Also, if it's not too difficult, I want to add it to excel as a button add-on so I can just click it once to auto-delete instead of running it as a Macro.

Hopefully it's a simple solution.

It's for Excel 2010 and 2013.
0
jetli87
Asked:
jetli87
  • 7
  • 7
1 Solution
 
byundtCommented:
Here is a macro that will delete rows that contain a numeric value less than + or - 5%, are blank or contain an error value. Cells containing text or values larger than + or - 5% (inclusive) will be preserved.
Sub Over5Percent()
Dim rg As Range
Dim i As Long
Application.ScreenUpdating = False
Set rg = Intersect(Selection, ActiveSheet.UsedRange)
For i = rg.Rows.Count To 1 Step -1
    If IsError(rg.Cells(i, 1).Value) Then
        rg.Rows(i).EntireRow.Delete
    ElseIf IsNumeric(rg.Cells(i, 1)) Then
        If Abs(rg.Cells(i, 1).Value) < 0.05 Then rg.Rows(i).EntireRow.Delete
    End If
Next
End Sub

Open in new window

0
 
byundtCommented:
If you store the macro in your Personal.xlsb macro workbook, then you can launch it with an icon on the QAT or ribbon.
0
 
jetli87Author Commented:
Thanks for the prompt reply.  Two follow up questions:

1) I should've specified this originally, but I need the macro to apply the conditions to a specific column, i.e. column "E".  Either statically code this in the macro or give a prompt to select the cell/column.

2) can you also include the condition to delete if the data = N/A?  For the data I work with, the cell is either a percentage or N/A.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
byundtCommented:
I defaulted the range selection to column E and displayed an input box to let you select the range. I also changed the macro to delete rows where the cell value is N/A (not the #N/A error value, which was already being deleted).
Sub Over5Percent()
Dim rg As Range
Dim i As Long
On Error Resume Next
Set rg = Application.InputBox("Please select a column of cells to test." & vbLf & _
        "If value is less than 5%, equals an error or N/A, that row will be deleted.", _
        Default:="E:E", Type:=8)
On Error GoTo 0

Application.ScreenUpdating = False
If Not rg Is Nothing Then
    Set rg = Intersect(rg, ActiveSheet.UsedRange)
    If Not rg Is Nothing Then
        For i = rg.Rows.Count To 1 Step -1
            If IsError(rg.Cells(i, 1).Value) Then
                rg.Rows(i).EntireRow.Delete
            ElseIf IsNumeric(rg.Cells(i, 1)) Then
                If Abs(rg.Cells(i, 1).Value) < 0.05 Then rg.Rows(i).EntireRow.Delete
            ElseIf UCase(rg.Cells(i, 1).Value) = "N/A" Then
                rg.Rows(i).EntireRow.Delete
            End If
        Next
    End If
End If
End Sub

Open in new window

0
 
jetli87Author Commented:
thanks again.  

And so sorry, but I was justed notified of a change, instead of deleting rows between -5% and 5%, the new value is to delete any rows that's less than "-500", i.e. -400, -200, 1, 1000, and N/A would be deleted.
0
 
byundtCommented:
the new value is to delete any rows that's less than "-500", i.e. -400, -200, 1, 1000, and N/A would be deleted.
I'm going to assume that the "1000" in that list was a typo. I'm also going to assume that you meant to say delete any rows whose absolute value is less than 500 or equals N/A.

I made changes to statements 6 and 18.
Sub DeleteRowsWithSmallValues()
Dim rg As Range
Dim i As Long
On Error Resume Next
Set rg = Application.InputBox("Please select a column of cells to test." & vbLf & _
        "If value is less than 500, equals an error or N/A, that row will be deleted.", _
        Default:="E:E", Type:=8)
On Error GoTo 0

Application.ScreenUpdating = False
If Not rg Is Nothing Then
    Set rg = Intersect(rg, ActiveSheet.UsedRange)
    If Not rg Is Nothing Then
        For i = rg.Rows.Count To 1 Step -1
            If IsError(rg.Cells(i, 1).Value) Then
                rg.Rows(i).EntireRow.Delete
            ElseIf IsNumeric(rg.Cells(i, 1)) Then
                If Abs(rg.Cells(i, 1).Value) < 500 Then rg.Rows(i).EntireRow.Delete
            ElseIf UCase(rg.Cells(i, 1).Value) = "N/A" Then
                rg.Rows(i).EntireRow.Delete
            End If
        Next
    End If
End If
End Sub

Open in new window

0
 
jetli87Author Commented:
Sorry, wasn't a typo.  So literally, if I have values, -1000, -500, -300, 200, 100, and 1000, the last four values (-300, 200, 100, and 1000) are to be deleted.  So the criteria is just to delete rows with values less than "-500".
0
 
byundtCommented:
I'm confused then.

Do you need to test one column, or more than one column?
Did you literally mean to delete rows where a value is less than -500? A value of -400 is larger than -500; a value of -600 is less than -500.

It might be clearer if you posted a sample workbook and indicated which rows needed to be deleted.
0
 
jetli87Author Commented:
Per the attached XLS sample, the logic would be applied to column "D" labeled "Variance." - There's two sheets, first one is "PreMacro" where every row that's highlighted in yellow would be deleted since the corresponding value in column "D" is GREATER THAN -500.

The second sheet, "PostMacro" shows the ideal result after running the code.

I apologize for getting the less than/greater than mixed up.

Can you also please include both code options, 1) that would prompt for cell selection, i.e. allowing the users to select D2-D29 and 2) where the cell selection is coded in the macro, i.e. user runs the macro which automatically applies it to whatever column/cell range I set in the VBA code.

I just wanted to sample both cause in some instances a user might want to manually select the cells.
Sample.xlsx
0
 
byundtCommented:
jetli87,
As written, the macro is asking the user to select a range. The user-choice defaults to column D.

I changed the macro so it deletes rows where the cell value is greater than -500, equals an error, or equals N/A.
Sub DeleteRowsWithSmallValues()
Dim rg As Range
Dim i As Long
On Error Resume Next
Set rg = Application.InputBox("Please select a single column range of cells to test." & vbLf & _
        "If value is greater than -500, equals an error or N/A, that row will be deleted.", _
        Default:="D:D", Type:=8)
On Error GoTo 0

Application.ScreenUpdating = False
If Not rg Is Nothing Then
    Set rg = Intersect(rg, ActiveSheet.UsedRange)
    If Not rg Is Nothing Then
        For i = rg.Rows.Count To 1 Step -1
            If IsError(rg.Cells(i, 1).Value) Then
                rg.Rows(i).EntireRow.Delete
            ElseIf UCase(rg.Cells(i, 1).Value) = "N/A" Then
                rg.Rows(i).EntireRow.Delete
            ElseIf rg.Cells(i, 1).Value = "" Then
            ElseIf IsNumeric(rg.Cells(i, 1)) Then
                If rg.Cells(i, 1).Value > -500 Then rg.Rows(i).EntireRow.Delete
            End If
        Next
    End If
End If
End Sub

Open in new window

I realize that you may not feel comfortable writing VBA code, but changing it to suit your purposes is not hard. I wrote the macro so you could modify it without needing to know much about VBA. And if you perform your tests on a copy of your actual data, you can't screw things up beyond a simple repair. Please give it a try.

Brad
SampleQ28385225.xlsm
1
 
jetli87Author Commented:
Simple Amazing!
0
 
jetli87Author Commented:
Can you please edit the sample to hardcode cells d4 to d200?

That would beyond perfect to have both.  Thanks 10000X!
0
 
jetli87Author Commented:
Actually, nevermind.  I was able to figure it out.  Thanks a bunch!!!!  Really appreciate the prompt responses.
0
 
byundtCommented:
I edited statement 7 in the attached workbook so it reads D4:D200.
        Default:="D4:D200", Type:=8)

Open in new window

SampleQ28385225.xlsm
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now