Deleting a row if it contains the right criteria

**TDN** · 11-19-2010, 01:13 AM

Hello,

I have a project that I'm working on and stuck at trying to figure this part out. It is in a larger database of information and I need a macro that will analyze one column of data and delete the entire row if it's value is under 30 sec.

Help!

**Simon Lloyd** · 11-19-2010, 01:40 AM

you dont say which column to look at so you can adjust this yourself!

Sub Del_If_under_30()
'Code supplied by Simon Lloyd
'19/11/2010
'Microsoft Office Help
'Excel Conference Poll
Dim rRow As Double, i As Double

rRow = Range("A" & Rows.Count).End(xlUp).Row
For i = rRow To 1 Step -1
    If Range("A" & rRow).Value < 30 Then
        Rows(rRow).EntireRow.Delete
    End If
Next i
End Sub

**lakshithaw** · 11-19-2010, 02:55 AM

With this one you can do the action for any selection of yours...

Option Explicit


Sub delete()
Dim dvalue As Double
Dim cell As Range


For Each cell In Selection

    If ActiveCell.Value < 30 Then
        ActiveCell.EntireRow.delete
        ActiveCell.Offset(-1, 0).Activate
    End If
        
        ActiveCell.Offset(1, 0).Activate
        Next cell
        
End Sub

**Simon Lloyd** · 11-19-2010, 03:39 AM

Originally Posted by lakshithaw

With this one you can do the action for any selection of yours...

Option Explicit
 
 
Sub delete()
Dim dvalue As Double
Dim cell As Range
 
 
For Each cell In Selection
 
    If ActiveCell.Value < 30 Then
        ActiveCell.EntireRow.delete
        ActiveCell.Offset(-1, 0).Activate
    End If
 
        ActiveCell.Offset(1, 0).Activate
        Next cell
 
End Sub

You will have lots of problems if you choose to do it this way, firstly selecting or activating all the time will slow excel down and is not necessary, secondly you should always delete from the bottom up