0
votes

I am trying to create a VBA script that will go through column "A" in my spreadsheet and delete any Row that starts with a cell that contains the words "Div Code:" The problem is, that the cells contain a lot more text after "Div Code" and I can't figure out how to make the .replace function work on partials. Here's what I have thus far:

Sub FindDivCode()


  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    .Replace "Div Code", "#N/A", xlPart
    .Replace "Date", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

This script works for the "Date" cells, but not the "Div Code" Cell's. Any help will be greatly appreciated!

Paul

2

2 Answers

0
votes

You could loop through the cells of the column A and, after having checked if the cell Ax starts by "Div Code", delete the entire row:

For j = Range("A1").End(xlDown).Row To 1 Step -1
    If InStr(Range("A" & j).Value,"Div Code") = 1 Then
        Rows(j).Delete
    End If
Next j
0
votes

This is an extension to the solution given by Matteo. Credit should go to him

    Sub FindDivCode2()
        For j = Range("A1").End(xlDown).Row To 1 Step -1
            If InStr(Range("A" & j).Value,"Div Code") = 1 Or  InStr(Range("A" & j).Value,"Date") = 1Then
                Rows(j).Delete
            End If
        Next j
    End Sub