Solved

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

Posted on 2014-03-10
14
6,775 Views
Last Modified: 2014-03-11
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
Comment
Question by:jetli87
  • 7
  • 7
14 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39919680
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
 
LVL 81

Expert Comment

by:byundt
ID: 39919684
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
 
LVL 1

Author Comment

by:jetli87
ID: 39921510
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 81

Expert Comment

by:byundt
ID: 39922151
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
 
LVL 1

Author Comment

by:jetli87
ID: 39922188
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
 
LVL 81

Expert Comment

by:byundt
ID: 39922237
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
 
LVL 1

Author Comment

by:jetli87
ID: 39922249
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
 
LVL 81

Expert Comment

by:byundt
ID: 39922324
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
 
LVL 1

Author Comment

by:jetli87
ID: 39922435
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
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39922460
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
0
 
LVL 1

Author Closing Comment

by:jetli87
ID: 39922481
Simple Amazing!
0
 
LVL 1

Author Comment

by:jetli87
ID: 39922483
Can you please edit the sample to hardcode cells d4 to d200?

That would beyond perfect to have both.  Thanks 10000X!
0
 
LVL 1

Author Comment

by:jetli87
ID: 39922493
Actually, nevermind.  I was able to figure it out.  Thanks a bunch!!!!  Really appreciate the prompt responses.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39922497
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question