VBA remove rows

I need VBA code to remove rows where
C = Unkn OR D = (blank)

Service Type      Package Type      Zone      Imp/Exp
IP      Box      A      Export
IP      Box      A      Import
IP      Box      B      Export
IP      Box      C      Export
IP      Box      C      Import
IP      Box      D      Export
IP      Box      F      (blank)
IP      Box      I      Export
IP      Box      J      Export
IP      Box      N      Export
IP      Box      O      Export
IP      Box      P      Import
IP      Box      PR      Export
IP      Box      Unkn      Import
TEST.xlsx
Euro5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
please try below code

Sub deleterow()
Dim x As Long
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = LastRow To 1 Step -1
    If UCase(Cells(x, "C").Value) = "Unkn" Then
        Rows(x).Delete
    End If
Next
On Error Resume Next
Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Open in new window

Excel amusantCommented:
Please try

Sub test()

Dim lrow As Long, c As Range

lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

For Each c In Range("C2:D" & lrow)

If c.Value = "Unkn" Then

c.EntireRow.Delete
End If
Next


For Each c In Range("D2:D" & lrow)

If c.Value = "(blank)" Then

c.EntireRow.Delete
End If
Next

End Sub

Open in new window

Anthony BerenguelCommented:
try this
Sub RemoveRows()
    
    Range("a5").Select
    
    Dim rowOffsetValue As Integer
    
    rowOffsetValue = 0
    
    Do Until ActiveCell.Offset(rowOffsetValue, 0).Value = ""
        
        If LCase(ActiveCell.Offset(rowOffsetValue, 2).Value) = "unkn" _
        Or ActiveCell.Offset(rowOffsetValue, 3).Value = "" Then
            '//remove row
            Dim rngRemove As Range
            Dim thisRow As Integer
            thisRow = ActiveCell.Offset(rowOffsetValue).Row
            Set rngRemove = Range(thisRow & ":" & thisRow)
            rngRemove.EntireRow.Delete
            rowOffsetValue = rowOffsetValue - 1
        End If
        'next row
        rowOffsetValue = rowOffsetValue + 1
    Loop
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Thanks so much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.