Results 1 to 5 of 5

Thread: Search and remove values ​​from a list

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14

    Search and remove values ​​from a list

    Hi,

    I have a list of values ​​to be removed more adjacent columns
    I produced the following code and I hoped would be faster in execution.
    Code:
    Option Explicit
    
    Sub PositionNumbers()
        'Disable these commands
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False
        
        Dim area As Range
        Set area = Range("T7:CG7")                                      'Range to be analyzed
        Dim Col As Variant, Colonna As Integer
        Dim NewList As Variant, EscList() As Variant
        Dim X As Variant
        Dim CicloA As Long, CicloB As Long
        EscList = Range("R8:R" & Range("R" & Rows.Count).End(xlUp).Row) 'Range with values to delete
        
        For Each Col In area
            Colonna = Col.Column
            NewList = Range(Cells(8, Colonna), Cells(Cells(Rows.Count, Colonna).End(xlUp).Row, Colonna))
            For CicloA = 1 To UBound(NewList)
                X = NewList(CicloA, 1)
                For CicloB = 1 To UBound(EscList)
                    If EscList(CicloB, 1) = X Then NewList(CicloA, 1) = "": Exit For
                Next CicloB
            Next CicloA
            Cells(8, Col.Column).Resize(UBound(NewList, 1)) = NewList
        Next Col
        
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        ActiveSheet.DisplayPageBreaks = False
        Application.ScreenUpdating = True
    End Sub
    Wonder if there are better performing codes

    thank you in advance

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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...
    Code:
    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

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    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...
    Code:
    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?

  4. #4
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    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

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by PcMax View Post
    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.

Similar Threads

  1. Search Directories to List Files VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 04-15-2014, 08:22 PM
  2. Replies: 7
    Last Post: 04-22-2013, 01:41 PM
  3. Replies: 3
    Last Post: 01-28-2013, 11:01 PM
  4. Copy Row To A New Sheet If A Value Found In Adjacent Column
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 08-17-2012, 05:42 PM
  5. List Unique Values Using Formula
    By LalitPandey87 in forum Excel Help
    Replies: 5
    Last Post: 01-09-2012, 08:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •