Results 1 to 15 of 15

How to Optimize This Code?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365
    Posts
    263

    How to Optimize This Code?

    Good night people,

    I have a task to do which I use a find and replace code that works by selection:

    Sub MultiFindNReplace()
    'Update 20140722
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
    Application.ScreenUpdating = False
       
    For Each Rng In ReplaceRng.Columns(1).Cells
        InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
    Next
    Application.ScreenUpdating = True
    End Sub
    The code is great and does the job, but it can take hours to process when I'm working on a 100K rows with cells to be found and replaced by 100k different matches from another worksheet.

    Could anyone suggest me something to improve the performance?


    PS: I'm not exactly sure how it affects the coding, but the column contains blank cells between the values. Maybe telling it to auto skip it would save it some time? (sorry if it sounds stupid, I'm kind of new with excel)
    Last edited by therealdees; 03-18-2021 at 08:30 PM.
    Pedro.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Optimize Code
    By floppygoat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2020, 04:52 AM
  2. optimize code if possible
    By mohadin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2019, 06:18 AM
  3. How can I optimize my code
    By viettest in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-08-2019, 05:02 PM
  4. Optimize a code
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2015, 05:31 PM
  5. Optimize code
    By DarkKnightLupo in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-19-2014, 08:58 AM
  6. Optimize code
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 03:35 PM
  7. Optimize VBA code
    By doodlebug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2007, 07:53 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1