Delete Unwanted Rows

zmagic · Aug 22nd 2022

HI,

I want to delete un-required rows from my workbook and those with CCODE -/00***/ must stay and other rows to get deleted.

There are more than 1000 rows and I want to clean them up.

Quote

**alansidman** · Aug 23rd 2022

Assume your data is in columns A, B and C. Here is a VBA solution

Code

Sub Foo
Dim lr as long, i as long
lr = Range("A" & rows.count).end(xlup).row
For i = lr to 2 step -1
If Instr(Range("C" & i), "CCODE -/00") = 0 then
Range("C" & i).EntireRow.Delete
End If
Next i
End Sub

**pike** · Aug 23rd 2022

Hi zMagic,

faster variation if you have thousands of rows to delete.

Code

Option Explicit
Sub ptest()
Dim dRange As Range
Dim dRow As Long

    For dRow = 1 To Cells(Rows.Count, "C").End(xlUp).Row
        If Not Cells(dRow, "C") Like "CCODE -/00###/*" Then
            If dRange Is Nothing Then
                Set dRange = Cells(dRow, "C")
            Else
                Set dRange = Union(dRange, Cells(dRow, "C"))
            End If
        End If
    Next dRow
    If Not dRange Is Nothing Then dRange.EntireRow.Delete

End Sub

Display More

**KjBox** · Aug 23rd 2022

Even faster, assumes header row is Row 1 and starts in Column A

Code

Sub DeleteRows()
    Dim x, i&
    
    With ActiveSheet.Cells(1).CurrentRegion
        x = .Value
        For i = 2 To UBound(x, 1)
            If Not x(i, 2) Like "*CCODE -/00###*" Then x(i, 1) = vbNullString
        Next
        .Value = x
        On Error Resume Next
        .Columns(1).SpecialCells(4).EntireRow.Delete
        On Error GoTo 0
    End With
    
End Sub

Display More

manaraziz96 · Aug 23rd 2022

hi all

how to delete rows based on cell value ?

thanks

**KjBox** · Aug 23rd 2022

mamaraziz, please ask your question in a new thread

Delete Unwanted Rows

Participate now!

Share

Similar Threads

Copy Paste as Values certain columns from Autofiltered data in 2 worksheets to 1 Summary worksheet