[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Is there any way to speed this up?

Keith74

12/15/2006 8:52:00 AM

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
5 Answers

Dave \Crash\ Dummy

12/15/2006 9:12:00 AM

0

Hi Keith,

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

Cheers

--
macropod
[MVP - Microsoft Word]


"Keith74" <keith.willis@sbjbc.co.uk> wrote in message news:1166172731.491630.112720@73g2000cwn.googlegroups.com...
| 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
|

NickHK

12/15/2006 9:23:00 AM

0

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

NickHK

"Keith74" <keith.willis@sbjbc.co.uk> wrote in message
news:1166172731.491630.112720@73g2000cwn.googlegroups.com...
> 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
>

Keith74

12/15/2006 9:26:00 AM

0

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

Bob Phillips

12/15/2006 10:00:00 AM

0

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


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Keith74" <keith.willis@sbjbc.co.uk> wrote in message
news:1166174732.280957.320590@l12g2000cwl.googlegroups.com...
> 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
>


Don Guillett

12/15/2006 1:33:00 PM

0

Post your code

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Keith74" <keith.willis@sbjbc.co.uk> wrote in message
news:1166172731.491630.112720@73g2000cwn.googlegroups.com...
> 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
>