CODEWITHSUNDEEP
excelvba
Nullifiable
Nullifiable

Reputation: 49

Excel CSV mass row delete

I have a csv file over 50k products. It being a csv file all information is in the A column alone, my objective here is to run through each row and find the records with "Warranty Extension" in them and then delete that row.

I tried the code below but I suspect that it's looking in column A with rows that ONLY include Warranty Extension. How would I customize this code to look through the rows that have Warranty Extension in them not ONLY Warranty Extension.

Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
    If (Cells(i, "A").Value) = "Warranty Extension" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
        Cells(i, "A").EntireRow.Delete
    End If
 Next i

Upvotes: 1

Views: 143

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Delete (Partial) Matches Using the Find Method

  • Using Find and FindNext, combines all matching cells into a range (Union). Finally deletes the entire rows of the range in one go.
Option Explicit

Sub deleteMatches()

    Dim Last As Long: Last = Cells(Rows.Count, "A").End(xlUp).Row
    Dim rg As Range: Set rg = Range("A1:A" & Last)
    
    Dim fCell As Range
    Set fCell = rg.Find("Warranty Extension", , xlFormulas, xlPart)
    
    If Not fCell Is Nothing Then
        Dim drg As Range
        Dim FirstAddress As String: FirstAddress = fCell.Address
        Do
            If drg Is Nothing Then
                Set drg = fCell
            Else
                Set drg = Union(drg, fCell)
            End If
            Set fCell = rg.FindNext(fCell)
        Loop Until fCell.Address = FirstAddress
        drg.EntireRow.Delete
    End If

End Sub

Upvotes: 1

Tim Stack
Tim Stack

Reputation: 3248

You need to use the Like operator and special characters to indicate other text may precede or follow your search string

Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
    If Cells(i, "A").Value Like "*Warranty Extension*" Then
        'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
        Cells(i, "A").EntireRow.Delete
    End If
Next i

The following wildcards are available for the Like operator:

  1. *: Zero or more characters
  2. ?: Any single character
  3. #: Any single digit (0–9)
  4. [charlist]: Any single character in charlist
  5. [!charlist]: Any single character not in charlist

I want to add that you're not specifying the workbook and worksheet the operations should be performed in, meaning that the code will be executed in whatever the active sheet is (something that can change mid code). Unless your code resides in a sheet module, I advise setting a Workbook and Worksheet variable to prevent code from executing on the wrong data:

Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Example.xlsx")
Set ws = wb.Sheets("Sheet1")

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

Upvotes: 2

Related Questions