Help on writing a Macro to delete rows with certain criteria

**cf7046** · 11-25-2012, 06:12 PM

Hello,

I am new to using Macros in excel (2003) and I am having problems writing a code to do the following.

I have a spreadsheet with 100 rows. I would like to add a button to the spreadsheet to delete all completed rows based on the "Yes or No" response in the cell. Anytime there is a Yes in column K, I would like the button to delete the rows and shift the information up. I have a formula in column B that auto numbers the task. In columns I and K I used data validation to have the user select the priority level and the Yes/No response.

The rows start on #6.

I have attached a sample spreadsheet.

Any help would be greatly appreciated.

Thanks!

**mike7952** · 11-25-2012, 06:32 PM

Give this a try

Sub abc()
 Const sh1 As String = "To Do List"
 Dim a, b, i As Long, ii As Long, n As Long
 
 With Worksheets(sh1)
    a = .Range("b5").CurrentRegion
 
    ReDim b(1 To UBound(a) - 1, 1 To UBound(a, 2) - 1)
    For i = 2 To UBound(a)
        If UCase(a(i, 10)) <> "YES" Then
            n = n + 1
            For ii = 2 To UBound(a, 2)
                b(n, ii - 1) = a(i, ii)
            Next
        End If
    Next
    .Range("c6").Resize(UBound(b), UBound(b, 2)) = b
 End With
End Sub