2

I am trying to delete all rows in Column E that contain 0. It is formatted as Currency and is giving me some issues.

Here is the code I am currently using but it is not working:

' Delete Anything in Column E with a hyphen

   Dim e As Range
   Dim rngToDelete2 As Range

    With Worksheets(5).Range("E1:E2000")
       Set e = .Find("0", LookIn:=xlValues)
       If Not e Is Nothing Then
           firstAddress = e.Address
           Do
               If rngToDelete2 Is Nothing Then
                  Set rngToDelete2 = e
               Else
                  Set rngToDelete2 = Union(rngToDelete2, e)
               End If
               Set e = .FindNext(e)
               If e Is Nothing Then Exit Do
           Loop While e.Address <> firstAddress
       End If
    End With

    If Not rngToDelete2 Is Nothing Then rngToDelete2.EntireRow.Delete

Not sure why, this works for other columns that contain hyphens or 0. Any help is appreciated.

Community
  • 1
  • 1
SASUSMC
  • 681
  • 4
  • 20
  • 38
  • 1
    The above code works for me. Have you seen [THIS](http://stackoverflow.com/questions/20077945/delete-cells-in-an-excel-column-when-rows-0) If you do not have large rows then you can also use autofilter as shown by @brettdj in that post. – Siddharth Rout Feb 17 '14 at 06:17
  • If it still doesn't work then I would like to see your workbook. This might ensure a faster resolution to the problem. If you are willing then upload it in sites like www.wikisend.com and then share the link here :) – Siddharth Rout Feb 17 '14 at 06:20
  • Will do shortly. Not sure why but it is leaving some of the rows. It is really strange. – SASUSMC Feb 17 '14 at 17:14
  • If you still didn't get an answer you can email me your file and i will take a look at it for you – Math4123 Feb 20 '14 at 21:54

1 Answers1

0

Not ideal but I ended up reformatting the column to be 0.000 as opposed to currency. This solved all the issues.

Thanks for all your help.

SASUSMC
  • 681
  • 4
  • 20
  • 38