Excel usedrange not updating

Your results may vary based on the math/comparisons you perform.

You can place the results in another array and insert that array into Excel worksheet (fastest) or update the worksheet directly from the comparison.

Of course you'd want to include the prefix and suffix suggested by hnk. Calculation = xl Calculation Automatic Application. Proposed algo (pseudocode): If what you are looking to do is Compare columns in Sheet 1 and Sheet 2 and then Copy the Matching numbers to Sheet 3, you are doing something that's at best O(n^2) in complexity, and looking at the implementation could easily be even more.

Cells(i, 1) j = j 1 next ii next i Should work MUCH faster than you code. Enable Events = False ' Your code goes here Application. Enable Events = True This might help speed things up a bit.

Wait wait Time ' Get number of rows for repeat loop upper limit...

When i run it , my code run it for 10 secs then excel freezing on me and not responding msg appear top of the screen. Then I would store my sorted arrays as cached columns elsewhere so that all further operations will take place in O(n). Edit: More discussion on the algo on this page: How to intersect two sorted integer arrays without duplicates?

NOW WHEN I COMPARE ONLY 100 NUMBERS BETWEEN TWO SHEETS, ITS WORK PERFECT AND NOT RESPONDING MSG DOESN'T APPEAR, it only occurs when I compare 10000 numbers together or larger amount of numbers. The fastest solution based on my experience is to move the data out of Excel worksheets (not out of Excel).

The Auto Fit Row Height does not apply and if you double click the row it defaults to 15.

below w/your own references): Sub process Excel Files() ' Declare variables Dim a(999999), i Dim Msg, style, Title Dim my Drive, my Location, Load Dir As String Dim counter, loop Limit ' Initialize variables... Wait wait Time 'Set the filepath to the correct directory... Display Status Bar = old Status Bar ' Define user dialog parameters Msg = "File processing is now complete." style = vb OKOnly vb Information vb Default Button1 Title = "File Processing Status" ' Display user dialog Response = Msg Box(Msg, style, Title) End If Exit Sub 'Error handler if no file(s) exist in directory...

' *** IMPORTANT *** Replace the drive name "C" below w/ your reference my Drive = "" ' Turn off screen updating (program runs faster)... Screen Updating = False ' Notify user of progress... Ch Drive my Drive Ch Dir my Drive & ":\" & my Location ' Initialize the array counter... Files Missing: ' Define user dialog parameters Msg = "There are no files located in the " & my Drive & ":\" & my Location & " directory." & Chr(13) & _ "The program stopped and no updates were made." style = vb OKOnly vb Critical vb Default Button1 Title = "Missing File(s)" ' Display user dialog Response = Msg Box(Msg, style, Title) ' Reset screen updating and status bar...

Status Bar = "Searching for files; please wait..." new Hour = Hour(Now()) new Minute = Minute(Now()) new Second = Second(Now()) 2 wait Time = Time Serial(new Hour, new Minute, new Second) Application.

i = 0 ' Load array with names of Excel files in correct directory... Close Save Changes:=True Next My Fil Count ' Reset screen updating and status bar...

Leave a Reply