Results 1 to 13 of 13

Optimize VBA Code without Copy&Paste

Threaded View

rwat1 Optimize VBA Code without... 09-24-2018, 01:03 AM
jindon Re: Optimize VBA Code without... 09-24-2018, 01:33 AM
rwat1 Re: Optimize VBA Code without... 09-24-2018, 01:44 AM
rwat1 Re: Optimize VBA Code without... 09-24-2018, 02:04 AM
jindon Re: Optimize VBA Code without... 09-24-2018, 02:14 AM
rwat1 Re: Optimize VBA Code without... 09-24-2018, 01:09 PM
rwat1 Re: Optimize VBA Code without... 10-07-2018, 09:53 PM
jindon Re: Optimize VBA Code without... 10-07-2018, 10:25 PM
rwat1 Re: Optimize VBA Code without... 10-07-2018, 10:52 PM
jindon Re: Optimize VBA Code without... 10-07-2018, 10:57 PM
rwat1 Re: Optimize VBA Code without... 10-08-2018, 10:31 PM
rwat1 Re: Optimize VBA Code without... 10-15-2018, 09:40 PM
rwat1 Re: Optimize VBA Code without... 08-29-2019, 01:24 PM
  1. #1
    Registered User
    Join Date
    09-19-2018
    Location
    Boston, MA
    MS-Off Ver
    Office 365
    Posts
    13

    Optimize VBA Code without Copy&Paste

    Dear Community,

    See Attached File.

    I have Jindon's code for "Sorting", but I'm trying to Loop through each column list, copy&pasting into Jindon's code for "Sorting".



    Why does Version1() macro work 100% fine (but takes 3 seconds per loop), but Version2() macro does not work at all, and gets poopy results?
    Sub version1()
    '
    ' this loops through using Jindon's "Test"  Macro
    '
    Dim count As Integer
    count = 0
    
    Do While count < 4   'looping through the first 4 columns as test, eventually want 16,000 columns
        Sheets("Sheet2").Activate
        ActiveSheet.Range("B2:B31").Offset(0, count).Select  'For B2:B31 it is a range everytime it loops, it offsets by 1 column
        Selection.Copy  'Copying Arf range from Sheet 2
        
        Sheets("Sheet1").Activate
        ActiveSheet.Range("A2").Select
        ActiveSheet.Paste  'Pasting into Jindon's code on Sheet 1
        
        Call test  'This Activates Jindon's Code
        
        ActiveSheet.Range("A2:A31").Select
        Selection.Copy   'Copying results of sorted column from Sheet 1
        Sheets("Sheet2").Activate
        ActiveSheet.Range("B2:B31").Offset(0, count).Select  'Pasting results into Sheet 2
        ActiveSheet.Paste
        Selection.Copy
        
        count = count + 1
    Loop
    
    End Sub

    Trying to OPTIMIZE with Version2()

    Sub version2()
    '
    ' this loops through using Jindon's "Test"  Macro
    '
    Dim count As Integer
    count = 0
    
    Do While count < 4   'looping through the first 4 columns as test, eventually want 16,000 columns
    
    
        
        Sheet1.Range("A2:A31") = Sheet2.Range("arf").Offset(0, count).Value
        Call test
        Sheet2.Range("arf").Offset(0, count) = Sheet1.Range("A2:A31").Value
        
        
        count = count + 1
    Loop
    
    End Sub
    I'm trying to Optimize the Version1() macro to eliminate any inefficiency, such as elimianting copy&paste, because I calculate it may take 26 hours to do 16,000 loops at 3 seconds each.
    Attached Files Attached Files
    Last edited by rwat1; 09-24-2018 at 01:19 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to optimize the code
    By pm.patel189 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2018, 01:06 PM
  2. [SOLVED] Optimize code copy very slow
    By JohnGreen2 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-21-2017, 04:20 AM
  3. Optimize a code
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2015, 05:31 PM
  4. Optimize code
    By DarkKnightLupo in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-19-2014, 08:58 AM
  5. Optimize code
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 03:35 PM
  6. Optimize VBA code
    By doodlebug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2007, 07:53 AM
  7. Optimize copy/paste loop
    By aldsv in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-09-2005, 06:07 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