Delete Rows containing specific character

[etseel]

I have an excel spreadsheet that imports various information using a DDE link to another application. Fields sometimes error out, and these fields are evidenced by the presence of the pound sign "#" character. Sample errors are "#NA Fld", "#NA Calc", etc.

I want my macro to delete every row that contains any cell containing a pound sign.

My code to delete rows with the pound sign works great. Now I need an efficient way to create a "Do While" loop or something. I don't want to simply run the loop once for each row because processing time is so long (usually a couple thousand records per file).

Can you help me with efficient looping code to continue running the Find/Delete loop while # signs still exist on the spreadsheet?

Here's my current code:
Sub POUND()
' POUND Macro
' This macro finds and deletes all records that have error fields not populated_
' by the DDE Server. Error fields contain the # character.

Range("A1").Select
' locate cells containing the # character
Cells.Find(What:="#", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
' now delete that row
ActiveCell.EntireRow.Delete Shift:=xlUp

' now loop back through until all rows with error fields are deleted

End Sub

 

[SkipVought]

Hi,

Sub POUND()
' POUND Macro
' This macro finds and deletes all records that have error fields not populated_
' by the DDE Server.  Error fields contain the # character.

    ' locate cells containing the # character
    Do
      set rng = Cells.Find(What:="#", After:=[A1], LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)
      if not rng is nothing then
        ' now delete that row
        rng.EntireRow.Delete Shift:=xlUp
      else
        exit do
      end if
    Loop

End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[etseel]

Skip, That is absolutely fantastic - exactly what I was looking for! Thank you very much.

This is great code, I've been looking for an easy way to do deletes like this for a couple years...

 

[tviman]

Skip,

I'm looking for a similar solution but my problem is that I need to delete rows that are empty. But here's the rub, the "empty" rows are not really empty... ISTEXT() returns TRUE, ISBLANK(), ISNONTEXT(), ISNA(),ISNUMBER() all return FALSE. The only condition I can find that will absolutely identify the row to be deleted is if LEN(cell reference) = 0.

How can your script be adapted to find those cells?

Thank You for any help you can provide.

Patrick

There's always a better way. The fun is trying to find it!

 

[SkipVought]

Patrick,

If you search this forum for delete rows, there is a plethora of examples. The key is working from the bottom up.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244