Can I speed up this VBA code?

I've put together a simple macro for sifting through column A and transposing every 3 rows into one row... (i.e. A1,A2,A3 go to A1,B1,C1 , A4,A5,A6 go to A2,B2,C2 etc... )

it works really well, however, I'll be pushing the scope to the max, i.e trying to go to rows.count

I was wondering if anyone has an insight on on how to speed up the code, it takes about 40 seconds to get through 200,000 lines, and sort of bombs out (91 error) sometime after that ....

any ideas on some improvements?

here's the code :

Sub arrsampWORKS1() Dim array_example(3) Dim Destination As Range Dim p As Double 'StartTime = Timer For q = 0 To 40 p = q * 3 'Storing values in the array For i = 0 To 2 array_example(i) = Range("A" & i + 1 + p) Next Set Destination = Range(Cells(q + 1, 4), Cells(q + 1, 7)) Set Destination = Destination.Resize(1, 3) Destination.Value = array_example Next 'MsgBox Timer - StartTime & " seconds" End Sub

-------------Problems Reply------------

In general, if I have a macro that I want to be more efficient, I turn off screen updating and auto calculation at the beginning of the macro. By default, every time there is a change, excel will update every formula in the open workbooks.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

At the end of the macro, I re-enable them

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

This cuts the time dramatically, but is only useful if you do not need to recalculate everything.

If you need to calculate at a given time in the macro, you can use

Application.Calculate 'calculate everything
wksht.Calculate 'calculate a specified worksheet

If you still need better performance, then start reworking your code.

Writing to the spreadsheet and setting ranges take time to initialize and generally you can save time by storing more items in memory and then write at the end.

I would change your code to have 2 loops - first to read all of the data and create a temp variable that holds the info, then a 2nd loop that outputs all of the data.

Sub revised()

Dim array_example(3) As Variant, alldata() As Variant
Dim Destination As Range, Data As Range
Dim p As Double, iCount As Double, iArraysCount As Double
Dim step As Integer

'StartTime = Timer
Set Data = Range("A1")
ReDim Preserve alldata(0)
iArraysCount = 0
step = 3

For iCount = 1 To 45 Step step
'Storing values in the array
For i = 0 To 2
array_example(i) = Data.Cells(iCount + i)
ReDim Preserve alldata(iArraysCount)
alldata(iArraysCount) = array_example
iArraysCount = iArraysCount + 1

Set Destination = Range("B1")
For iCount = 0 To UBound(alldata)
Destination.Cells(iCount + 1, 1).Value = alldata(iCount)(0)
Destination.Cells(iCount + 1, 2).Value = alldata(iCount)(1)
Destination.Cells(iCount + 1, 3).Value = alldata(iCount)(2)
'MsgBox Timer - StartTime & " seconds"

End Sub

Category:excel Views:0 Time:2019-01-11

Related post

  • How can I speed up this Rails code? 2011-05-26

    It's a vague question I know....but the performance on this block of code is horrible. It takes about 15secs from the original post to the action to rendering the page... The purpose of this action is to retrieve all Occupations from a CV, all the sk

  • Can you Speed up a VBA code that takes hours. The code copies each of the six different section of a list and passes it to a specified sheet, either overlapping or to the bottom. 2013-07-28

    I have downloaded a list from internet containing sections of data from the following indices OBX, OSEBX, DAX, SPX, DIJ and ESFUT1. The formatted data is imported to sheet Datadump and further copied and passed unformatted to sheet Data. Below is sho

  • How can I speed up this IronPython code that generates a string of arbitrary size 2015-01-19

    I have a python script that runs within a C# application (the source of which I cannot control), and I want this script to generate a file of an arbitrary size. The data in this file does not matter. I wrote the following code: def generateFile(filen

  • MySQL, how can I speed up this query which takes 1.4 second? 2011-01-18

    How can I speed up this query ? SELECT PadID, CatID, IconSoureURL, OsStr, PadURL, PageName, ProgramName, ProgramVersion, ReleaseStatus, English45, License, DownloadURL FROM Pads WHERE RemoveMeDate = '2001-01-01 00:00:00' ORDER BY VersionAddDate DESC

  • Can someone explain what this ajax code does? 2011-05-24

    Can someone explain what this ajax code does? function ajaxProgress(){ //Math.random() is for bitchy ie to prevent caching the xml. $.get('sample.ff?do=progressInfo&type=sampletype&dummy='+Math.random(), { dataType: 'xml'}, function(xml) { //

  • I use Windows XP home edition. It takes awhile to bootup when I turn on the computer. How can I speed up this process? 2012-02-26

    I use windows XP home edition. It takes awhile to bootup when I turn on the computer. How can I speed up this process? original title: Slow Bootup --------------Solutions------------- Faster processor. Faster hard drive. Faster and/or more RAM/memory

  • Is there any way I can speed up this VBA algorithm? 2011-10-07

    I am looking to implement a VBA trie-building algorithm that is able to process a substantial English lexicon (~50,000 words) in a relatively short amount of time (less than 15-20 seconds). Since I am a C++ programmer by practice (and this is my firs

  • How can I speed up this block of code? 2012-01-10

    I have a lot of duplicate records that I'm trying to weed out, and to do that, I'm currently running this: do |survey| survey.response_sets.completed.each do |set| answer_ids = [] set.responses.each do |r| if r.answer.blank? r.dest

  • How can I speed up this method which removes text from a string? 2010-01-15

    I wrote the following method to remove the namespace in brackets from strings. I would like to make this as fast as possible. Is there a way to speed up the following code? using System; namespace TestRemoveFast { class Program { static void Main(str

  • How to speed up this Python code? 2010-11-17

    I've got the following tiny Python method that is by far the performance hotspot (according to my profiler, >95% of execution time is spent here) in a much larger program: def topScore(self, seq): ret = -1e9999 logProbs = self.logProbs # save indi

  • Is it possible to write this VBA code any better? 2011-11-04

    Am I reinventing the wheel here? Is there a better way to do this? This VBA function looks for the first instance of a string in the comment field of a form in Access containing 20 characters or less, no spaces, surrounded by (~) tildes, then returns

  • speed up this matlab code? 2011-11-16

    Is there a good way to speed up this block of matlab code (n in particular, can be large) using matrix operations, or anything? Over 1/4 of my execution time is in this small block of code. % Get the bin indexes that we will place the network in bins

  • Will this VBA code execute Asynchronously 2012-04-15

    Ok so I am trying to automate Microsoft Access from C#. Obviously you can't execute VBA code Asynchronously in VBA itself, but my idea was to brute force this from C# using delegates. We have a legacy reporting system which runs hundreds of badly des

  • Can't edit a projects VBA code 2013-04-29

    I've been given a project called FTP which I am required to test, to a degree. The problem I've come across is that I can't edit any of the code in this project (You may be wondering why I am editing code to test - this isn't a formal test phase by a

  • This VBA Code will crash Excel 2010 - why? 2013-06-25

    I was attempting to assist a user who needed all worksheets to have their columns in the same sequence as the first sheet in the workbook. I thought the fast way to do it would be to set up a custom list using labels from the first sheet and then sor

  • Excel Crashes and Restarts whenever worksheet is deleted after running this VBA Code 2014-06-10

    When I drill down into my pivot table and get another worksheet with the details behind the pivot table I run this very simple VBA code to format the sheet so that it looks presentable. The code performs exactly as it should. But when I delete the wo

  • How can I speed up this linq to sql function? 2008-12-23

    I have a function (called "powersearch", the irony!) that searches for a set of strings across a bunch(~ 5) of fields. The words come in as one string and are separated by spaces. Some fields can have exact matches, others should have "contains". (Sn

  • Can some please explain this jQuery code? 2009-12-15

    Possible Duplicate: Can someone Explain this jQuery code? I have posted this before, but I would like to refine my question (and I can't seem to do it in the old thread). The code is: $(document).ready(function() { var rot=$('#image3').rotate({maxAng

  • Is there tool that can port or compile excel VBA code to C#? 2010-02-26

    I don't know much about .net, but I am working on a project where a requirement is to port some Excel VBA code embedded in spreadsheets to standalone C# . Are they any tools that could automate (or semi-automate) this port or compile? How well do the

  • Speeding up this jQuery-code with selectors 2010-12-16

    i wrote a filter for a list of many objects. Users can choose their options in via radio-buttons. Within my filter functionality i select the checked radio buttons of both radio groups/sets: if ( $('#filter input[name="interval"]:checked').val() == '

  • Can someone help with this install code error ( ox8007006E ) 2012-07-06

    Can someone help with this code error ( ox8007006E ) --------------Solutions------------- Not without at least a little context - what are you attempting to do when you get the error message? I down load this windows 7 find but the number I written d

  • Python is a bit slow. How can I speed up this code? 2011-04-15

    Python is disappointing me. I searched a code for port scan, found this. Runned it. the program I used for scanning. It was very fast according to python code. the code is below. Can you help me about accelerating my code. What can i du for it?. #!/u

  • Can anyone speed up this code? 2011-05-14

    I've got this bit of php here that is very sluggish. Not millisecond sluggish, but causes the page to load in 8 seconds sluggish. What it is doing is pulling Actor and Director information from a 3rd party movie database with a json string, for each

  • Any advice to speed up this 2007 code? (already have screenupdating, calculation turned off) 2013-09-16

    Using Excel 2007, and would welcome any ideas on how to speed this code up. Due to the size of the source data, it takes more than 30 minutes to complete (and as monthly data is added, will continue to take longer and longer). As noted in the code, I

  • How can I speed up this SELECT CONCAT/GROUP BY query? 2009-07-31

    I'm working on selecting locations (city, state) out of a database. The problem is that the query is running a tad slow and I'm not sure how to speed it up. For example: SELECT CONCAT_WS(', ', city, state) as location, AVG(latitude), AVG(longitude) F

Copyright (C), All Rights Reserved.

processed in 0.234 (s). 11 q(s)