Solved

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

Posted on 2014-03-10
14
6,904 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Simple Linear Regression
Starting up a Project

724 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