1

I'm trying to write some code that will delete any row where the character "," or Chr(44) is not found in any of the cells in that row.

I've gotten this far but am struggling because the code is only searching column C for "," but I need it to search the entire current row.

How can I get this updated?

Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long


' Defines LastRow as the last row of data based on column C
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).row


' Sets check range as C1 to the last row of C
Set cRange = Range("C1:C" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
        If .value <> "Chr(44)" Then
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x


End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Valborg
  • 79
  • 6
  • 1
    First thing the line If .value <> "Chr(44)" Then should read If .value <> Chr(44) Then. That is, remove the quotes around Chr(44). As it stands, its comparing the text string "Chr(44)" and not ",". – John F Aug 20 '18 at 14:30
  • 2
    Deleting a row in a loop is a bad idea. You may want to consider [THIS](https://stackoverflow.com/questions/39620661/entirerow-delete-skips-entries-in-for-loop) approach – Siddharth Rout Aug 20 '18 at 14:49

1 Answers1

6

Probably easier to use the Find method of Range object, rather than doing cell-by-cell iteration. And you can use the EntireRow property of each cell Range to do this:

For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x).EntireRow
        If .Find(Chr(44)) Is Nothing Then
            .Delete
        End If
    End With
Next

Also note per John's comment on OP, you were (mistakenly) comparing against a string literal "Chr(44)" when you need to be comparing against the evaluation of Chr(44) function, which returns a string: ",".

David Zemens
  • 53,033
  • 11
  • 81
  • 130