Speed problem using Offset in VBA 
Author Message
 Speed problem using Offset in VBA

I have a spreadsheet that lists data in a report from two
seperate datasources that are related.  It will list a
header row then N number of detail rows about that header
and repeat this for N number of header rows.

In order to make this work I have 2 lists of data, 1 for
the header information and 1 for the detail information.  
Each set has a "Primary Key" in common that they can be
linked together on.  I use a loop to select each header
individually, then count the number of rows in that exists
in the detail data for the related detail records.  Then
starting at the first detail row of the related records I
loop through each detail record and copy it to the
appriopriate place in the report.  This copy is done with
the OFFSET function.

OFFSET allows me to copy each column and row field for
each detail record to the appriopriate place in the report
for only the number of detail records that exist for the
header records.

My problem is that this method is very slow in one of my
worksheets.  I have the exact same code and data in two
worksheets and in one the copying will execute in under 1
second, in the other it will take 2 minutes.  

Does anyone know what may affect the speed of the OFFSET
function?

Thanks!
Turning Gray



Sun, 20 Nov 2005 19:36:20 GMT  
 Speed problem using Offset in VBA
Haven't looked into the detail of your problem, but generally this kind of
thing is much faster when you dump the relevant range in a VBA array, do the
calculations in the array and dump the relevant data back to the sheet.
Have seen a factor 100 speed improvement with this approach.

RBS


Quote:
> I have a spreadsheet that lists data in a report from two
> seperate datasources that are related.  It will list a
> header row then N number of detail rows about that header
> and repeat this for N number of header rows.

> In order to make this work I have 2 lists of data, 1 for
> the header information and 1 for the detail information.
> Each set has a "Primary Key" in common that they can be
> linked together on.  I use a loop to select each header
> individually, then count the number of rows in that exists
> in the detail data for the related detail records.  Then
> starting at the first detail row of the related records I
> loop through each detail record and copy it to the
> appriopriate place in the report.  This copy is done with
> the OFFSET function.

> OFFSET allows me to copy each column and row field for
> each detail record to the appriopriate place in the report
> for only the number of detail records that exist for the
> header records.

> My problem is that this method is very slow in one of my
> worksheets.  I have the exact same code and data in two
> worksheets and in one the copying will execute in under 1
> second, in the other it will take 2 minutes.

> Does anyone know what may affect the speed of the OFFSET
> function?

> Thanks!
> Turning Gray



Sun, 20 Nov 2005 20:07:40 GMT  
 Speed problem using Offset in VBA
T.G. :

The fact that two almost identical situations producing such different
runtimes leads me to believe that Excel is taking a long time on the
CopyPastes on the laggard worksheet.

Step through the code in each sheet. If the problem worksheet has
significant pauses in the Copy and/or Paste, the worksheet is probably
becoming corrupted. I would rebuild that workbook.

I've had similar problems. After rebuilding workbooks that had many
CopyPastes or InsertRowsColumns, I rewrote the code to eliminate
copying/pasting and inserting/deleting rows/columns. This eliminated
my problems with this issue.

HTH
Paul

-----------------------------------

On Wed, 4 Jun 2003 11:36:20 -0700, "David Masciangelo"

Quote:

>I have a spreadsheet that lists data in a report from two
>seperate datasources that are related.  It will list a
>header row then N number of detail rows about that header
>and repeat this for N number of header rows.

>In order to make this work I have 2 lists of data, 1 for
>the header information and 1 for the detail information.  
>Each set has a "Primary Key" in common that they can be
>linked together on.  I use a loop to select each header
>individually, then count the number of rows in that exists
>in the detail data for the related detail records.  Then
>starting at the first detail row of the related records I
>loop through each detail record and copy it to the
>appriopriate place in the report.  This copy is done with
>the OFFSET function.

>OFFSET allows me to copy each column and row field for
>each detail record to the appriopriate place in the report
>for only the number of detail records that exist for the
>header records.

>My problem is that this method is very slow in one of my
>worksheets.  I have the exact same code and data in two
>worksheets and in one the copying will execute in under 1
>second, in the other it will take 2 minutes.  

>Does anyone know what may affect the speed of the OFFSET
>function?

>Thanks!
>Turning Gray



Sun, 20 Nov 2005 20:29:14 GMT  
 Speed problem using Offset in VBA
I am not actually using a Copy/Paste, I am assigning a
Cells.Value from another Cells.Value in VBA.  I would
imagine that since I am avoiding the clipboard altogether
it would be faster...?

I am not familiar with rebuilding a workbook, do you mean
recreating it from scratch?

Quote:
>-----Original Message-----
>T.G. :

>The fact that two almost identical situations producing
such different
>runtimes leads me to believe that Excel is taking a long
time on the
>CopyPastes on the laggard worksheet.

>Step through the code in each sheet. If the problem
worksheet has
>significant pauses in the Copy and/or Paste, the

worksheet is probably
Quote:
>becoming corrupted. I would rebuild that workbook.

>I've had similar problems. After rebuilding workbooks
that had many
>CopyPastes or InsertRowsColumns, I rewrote the code to
eliminate
>copying/pasting and inserting/deleting rows/columns. This
eliminated
>my problems with this issue.

>HTH
>Paul

>-----------------------------------

>On Wed, 4 Jun 2003 11:36:20 -0700, "David Masciangelo"

>>I have a spreadsheet that lists data in a report from
two
>>seperate datasources that are related.  It will list a
>>header row then N number of detail rows about that
header
>>and repeat this for N number of header rows.

>>In order to make this work I have 2 lists of data, 1 for
>>the header information and 1 for the detail
information.  
>>Each set has a "Primary Key" in common that they can be
>>linked together on.  I use a loop to select each header
>>individually, then count the number of rows in that
exists
>>in the detail data for the related detail records.  Then
>>starting at the first detail row of the related records
I
>>loop through each detail record and copy it to the
>>appriopriate place in the report.  This copy is done
with
>>the OFFSET function.

>>OFFSET allows me to copy each column and row field for
>>each detail record to the appriopriate place in the
report
>>for only the number of detail records that exist for the
>>header records.

>>My problem is that this method is very slow in one of my
>>worksheets.  I have the exact same code and data in two
>>worksheets and in one the copying will execute in under
1
>>second, in the other it will take 2 minutes.  

>>Does anyone know what may affect the speed of the OFFSET
>>function?

>>Thanks!
>>Turning Gray

>.



Sun, 20 Nov 2005 22:41:19 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. MS Excel VBA problem while using selection.offset and AutoFilter

2. Using VBA to expand offset selection

3. OFFSET(MATCH) Problem in VBA

4. I have a print speed problem using Publisher2000 and a Brother MF.

5. problem with .lookin property when using using filesearch in vba excel 2000

6. if activecell.offset()=activecell.offset

7. Use of Offset in Access VBA doesn't work

8. VBA, Lookup, Offset, etc

9. vba how to......offset

10. vba How to... offset


 
Powered by phpBB® Forum Software © phpBB Group