Search and remove values ​​from a list

[Rick Rothstein]

I do not know if this is faster than your code or not (you will have to test it to see), but it involves a whole lot less looping...

Sub PositionNumbers()
  Dim N As Long, LastRow As Long, ListToDelete As Variant
  ListToDelete = Range("R8:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  LastRow = Columns("T:CG").Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Application.ScreenUpdating = False
  For N = 1 To UBound(ListToDelete)
    Range("T8:CG" & LastRow).Replace ListToDelete(N, 1), "", xlPart
  Next
  Application.ScreenUpdating = True
End Sub

[Rick Rothstein]

I do not know if this is faster than your code or not (you will have to test it to see), but it involves a whole lot less looping...

Sub PositionNumbers()
  Dim N As Long, LastRow As Long, ListToDelete As Variant
  ListToDelete = Range("R8:R" & Cells(Rows.Count, "R").End(xlUp).Row)
  LastRow = Columns("T:CG").Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Application.ScreenUpdating = False
  For N = 1 To UBound(ListToDelete)
    Range("T8:CG" & LastRow).Replace ListToDelete(N, 1), "", xlPart
  Next
  Application.ScreenUpdating = True
End Sub

I see you have been online at least 3 times since I posted the above code... just wondering if you had a chance to try it out yet or not?

[PcMax]

Hi,

I see you have been online at least 3 times since I posted the above code... just wondering if you had a chance to try it out yet or not?

Rick I tried the code and it works perfectly
I tried with little data to analyze, and I always time 0.015 seconds

The database in which it must operate had the following search time ...
I wrote my code to reduce the search time from: 60:00 minutes 6:00 minutes
Insert an update when i can test with a complete database.

Greetings from Gian

[Rick Rothstein]

Rick I tried the code and it works perfectly
I tried with little data to analyze, and I always time 0.015 seconds

The database in which it must operate had the following search time ...
I wrote my code to reduce the search time from: 60:00 minutes 6:00 minutes
Insert an update when i can test with a complete database.

I'll be interested in how it does compared to your existing code. I am guessing that your complete database must be quite large as the approach you took in the code you posted (while I think it could be "tightened up" a small amount) does not look to be inefficient... given that, taking 6 minutes to run would seem to indicate a rather large amount of data being processed.