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.
LVL 1
jetli87Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.