Macro to delete rows not containing "-" in column A.

[imimin]

Hello!

I have the following macro that will 'examine' column A and delete any row that contains "-".

***What I need for it to do is to delete rows that DO NOT contain the hythen ("-") in column A. Can someone please help me re-write this?

Sub delrowsB()
    Dim i As Long
        
        For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
            'If InStr(Cells(i, "A").Text, "Pc.") Or InStr(Cells(i, "A").Text, "Total") Or InStr(Cells(i, "A").Text, "Delivery") Then
             If InStr(Cells(i, "A").Text, "-") Then
                Cells(i, "A").EntireRow.Delete
            End If
        Next i
            
End Sub

Thank you!

 

[brettdj]

One quick way (VBA or manullay) is to use a working column to test for the hypen, then use AutoFilter to kill the TRUE rows

This code inserts a formula in the first blank column checking A2 down to the last used cell in column A
=ISERROR(FIND("-",$A2))
As a check for TRUE rows to be deleted

Cheers

Dave

Sub KillnonDash()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
    Application.ScreenUpdating = False
    Rows(1).Insert
    With rng3.Offset(0, 1)
        .FormulaR1C1 = "=ISERROR(FIND(""-"",RC1))"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
        .EntireColumn.Delete
    End With
    Rows(1).Delete
    Application.ScreenUpdating = True
End Sub

 

[imimin]

Thank you! That works GREAT!

 

[imimin]

What if I have a header, how would you modify this code?

 

[brettdj]

The code currently looks to work from row 1 till the last used rows with this line

  Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))

so to work with a header row we need to work from the second cell, and use row 2 for an insert and delete row for the filter

hth

Dave

Sub KillnonDash()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(2, rng1.Column))
    Application.ScreenUpdating = False
    Rows(2).Insert
    With rng3.Offset(0, 1)
        .FormulaR1C1 = "=ISERROR(FIND(""-"",RC1))"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
        On Error Resume Next
        'in case all rows have been deleted
        .EntireColumn.Delete
        On Error GoTo 0
    End With
    Rows(2).Delete
    Application.ScreenUpdating = True
End Sub

 

[imimin]

Thanks again!