Solved

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

Posted on 2014-03-10
14
6,747 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now