Is there any way to speed this up?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith74 - 15 Dec 2006 08:52 GMT

Hi

I've got a bit of code that scans through each cell in the first column
of a spreadsheet using the offset method looking for pagebreaks, and
inserting a new line and some text if it finds one. It takes about
20secs to do just 200 rows. I've set application.visible to false and
application.screenupdating to false. Does anyone know of a way i can
speed this process up?

Thanks

Keith

Reply to this Message

macropod - 15 Dec 2006 09:11 GMT

Hi Keith,

That depends on your code - which you haven't posted.

Cheers

Signature

macropod
[MVP - Microsoft Word]

| Hi
|
[quoted text clipped - 8 lines]
|
| Keith

Reply to this Message

Keith74 - 15 Dec 2006 09:25 GMT

Hi

Here it is

For intCount = 1 To intTotal

If Worksheets("Summary").Rows(intCount).PageBreak <>
xlPageBreakNone _
And ActiveCell.Value <> "" Then
Rows(intCount).Select
Selection.Insert Shift:=xlDown
End If

ActiveCell.Offset(1, 0).Select
Next

cheers

Reply to this Message

Bob Phillips - 15 Dec 2006 09:59 GMT

removing the selecting will help

With Worksheets("Summary")

iRow = ActiveCell.Row
iCol = ActiveCell.Column

For intCount = 1 To intTotal

If .Rows(intCount).PageBreak <> xlPageBreakNone _
And .Cells(iRow + i - 1, iCol).Value <> "" Then

.Rows(intCount).Insert Shift:=xlDown

End If

Next

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi
>
[quoted text clipped - 13 lines]
>
> cheers

Reply to this Message

NickHK - 15 Dec 2006 09:22 GMT

Keith,
You can use the HPageBreaks/VPageBreaks collection to see where the occur.
Worksheets(1).HPageBreaks(1).Location.Address

NickHK

> Hi
>
[quoted text clipped - 8 lines]
>
> Keith

Reply to this Message

Don Guillett - 15 Dec 2006 13:32 GMT

Post your code

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi
>
[quoted text clipped - 8 lines]
>
> Keith

Reply to this Message