(Excel 2003 VBA) Delete entire rows based on multiple conditions in a column

Question

I have an interesting issue. I've tried searching this site and Google but there are only slightly related problems, none which actually address this specific need.

I have a spreadsheet with 10 columns (let's call them A-J). I need to delete all the rows that do NOT have a value of "30", "60", "90", "120" or blank within the cells of column H.

Though there are many ways of doing this, all of them have relied on loops, which doesn't work for me as this dataset has over 25k rows and it takes 10+ minutes to run - too long.

I've been looking at autofilter options in conjunction with the .Find function (e.g. find all rows with H cells that don't meet the criteria and delete) but AutoFilter on 2003 only works with 2 criteria, while I have 5 to check against. I'm not sure how to proceed.

Any help is appreciated.

You could process this quickly using a loop if you first read the entire dataset into a 2-d array, and copy the "good" rows into a second same-sized array. Then dump the "good" array back to the sheet. Unless you have cell formatting which you need to preserve. Maybe show the code you have already which is too slow? — Tim Williams
– Tim Williams, Commented Jul 10, 2013 at 5:50

Tim Williams · Accepted Answer · 2013-07-10 06:11:34Z

2

This deleted all matching rows (~10%) in a sample of 25k rows in 20sec

Sub tt()

Dim rw As Range
Dim all As Range
Dim t
Dim b As Boolean

t = Timer
For Each rw In Range("A1").CurrentRegion.Rows

    If rw.Cells(8).Value < 1 Then

        If b Then
            Set all = Application.Union(rw, all)
        Else
            Set all = rw
            b = True
        End If

    End If

Next rw

If not all is nothing then all.EntireRow.Delete

Debug.Print "elapsed: " & Timer - t

End Sub

answered Jul 10, 2013 at 6:11

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rangan · Accepted Answer · 2013-07-10 06:11:26Z

0

You can try Advanced Filter option where you can give more than two criteria to filter the list. After filtering the list matching the criteria you set, the filtered list can be copied to another location (option available) and the remaining deleted.

answered Jul 10, 2013 at 6:11

rangan

1

Comments

d-stroyer · Accepted Answer · 2013-07-10 06:13:19Z

0

You can add a column with the condition of your own:

=IF(OR(H1=30;H1=60;H1=90;H1=120;H1="");"DELETE";"")

(the formula is given for row 1, you have to copy-paste it to the entire range)

Then use filtering and sorting to select the rows to delete.

answered Jul 10, 2013 at 6:13

d-stroyer

2,7262 gold badges22 silver badges32 bronze badges

Comments

LS_ᴅᴇᴠ · Accepted Answer · 2013-07-10 08:52:00Z

Some speed tips:

When using large data, assign values to array and use array instead of *.Value;
When working with full columns, ignore empty columns at bottom;
When making intensive changes in worksheet, disable screen update and automatic calculation.

Stating this, I would use this code:

Sub Macro1()
    Dim su As Boolean, cm As XlCalculation
    Dim r As Long, v(), r_offset As Long

    su = Application.ScreenUpdating
    Application.ScreenUpdating = False 'Disable screen updating
    cm = Application.Calculation
    Application.Calculation = xlCalculationManual 'Disable automatic calculation

    'Only use used values
    With Intersect(Range("H:H"), Range("H:H").Worksheet.UsedRange)
        v = .Value 'Assign values to array
        r_offset = .Row - LBound(v) 'Mapping between array first index and worksheet row number
    End With

    'Check all row from bottom (so don't need to deal with row number changes after deletion)
    For r = UBound(v) To LBound(v) Step -1
        Select Case v(r, 1)
        Case "30", "60", "90", "120", Empty 'Do nothing
        Case Else
            Sheet1.Rows(r + r_offset).EntireRow.Delete
        End Select
    Next
    Application.ScreenUpdating = su 'Restore screen updating
    Application.Calculation = cm 'Restore calculation mode
End Sub

nclfrk799 · Accepted Answer · 2013-07-11 23:01:46Z

0

Thanks to all who've suggested solutions. In the between time I ended up figuring out a way to do this in <1 second - apparently I myself didn't realise that AutoFilter could've supported comparison criteria (greater than, less than etc).

Using a series of autofilters I simply filtered for, then deleted all rows that filtered to "<30", "30120".

Not elegant, but it did the trick.

answered Jul 11, 2013 at 23:01

nclfrk799

352 silver badges8 bronze badges

Collectives™ on Stack Overflow

(Excel 2003 VBA) Delete entire rows based on multiple conditions in a column

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related