1

I have the code below (loop) to search for 0's in my spreadsheet (Column D) when it finds one if performs a copy/paste and then deletes the row. After all the filtered 0's (the column is filtered by column A - duplicates) I tell it to end sub. But I found the find is finding the 0's in the filtered hidden rows so the loop keeps going.

How can make the find only work on the visible rows and then end when all the 0's have been dealt with.

Set RangeObj = Cells.Find(What:="0", After:=ActiveCell, _
 LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext, MatchCase:=False)

 If RangeObj Is Nothing Then RangeObj.Activate

 Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
 xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
 , SearchFormat:=False).Activate
Community
  • 1
  • 1
James
  • 19
  • 5

1 Answers1

0

What you need is the SpecialCells(xlCellTypeVisible) method and the .FindNext method.

See the below code:

Set RangeObj = Cells.SpecialCells(xlCellTypeVisible).Find(What:="0", After:=Range("A1"), _
                 LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)

If Not RangeObj Is Nothing Then

    Dim sFirstAdd As String, sAdd As String
    sFirstAdd = RangeObj.Address

    Do

        sAdd = RangeObj.Address
        With RangeObj.EntireRow 'or limit to just the necessary columns
            .Copy 'choose your desired destination
            .Delete
        End With

        Set RangeObj = Cells.SpecialCells(xlCellTypeVisible).FindNext(After:=Range(sAdd))

    Loop Until RangeObj Is Nothing Or sAdd = sFirstAdd

End If
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • I tried your code and now on the Set RangeObj I am getting a Type Mismatch: Runtime Error 13. Googled it couldn't find a solution. Thoughts? – James Feb 12 '16 at 15:56
  • @james - sorry, I edited the post. Try now. If you still get error, tell me which line. – Scott Holtzman Feb 12 '16 at 16:54
  • what did you change? – James Feb 12 '16 at 17:05
  • @James - I changed the way it checks for `RangeObj` after it deletes the found row, by using the `sAdd` variable. – Scott Holtzman Feb 12 '16 at 17:08
  • Still the same; Type Mismatch on the Set RangeObj = Cells.SpecialCells(xlCellTypeVisible).Find(What:="0", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) – James Feb 12 '16 at 17:16
  • Partial fix. Prior to the Set RangeObj I made A1 the active cell. I noticed before the find that the active cell was a hidden on so by making A1 the active cell I eliminated the Type Mismatch. Now the new problem. The macro reads the Set/Find but does not find the 0's on the spreadsheet. I just stays at cell A1. – James Feb 12 '16 at 18:13
  • @James change `xlFormulas` to `xlValues`. You probably want to change `xlPart` to `xlWhole` as well, but I don't know for sure. – Scott Holtzman Feb 12 '16 at 19:03
  • weirder by the minute. I did your changes and it found the row with the first 0 but kept the active cell as A1. I added activecell=RangeObj.select after the find and it moved the active cell to the first 0, but then changed the 0 to TRUE. What's that about??? – James Feb 12 '16 at 19:27
  • @james - [stay away from using `ActiveCell`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). I changed `After:=ActiveCell` to `After:=Range("A1")`. There is no need to select or activate any cells with this code. – Scott Holtzman Feb 12 '16 at 20:12
  • Thanks for your help. Leaving now for the weekend. Back at it on Monday...Thanks again. – James Feb 12 '16 at 20:16