VBA remove rows

Euro5
Euro5 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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

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

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

Author

Commented:
Thanks so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial