Programatically Scrolling Whilst Populating Spreadsheet Without Crashing

[RowanB]

Hi Everyone,

I've made an Excel VBA application that populates a 14 column, 2000 row table with data row by row. When it starts populating a new row, it uses application.Goto to select the row that's being populated. I really like this idea because it stops the user getting bored waiting for it to finish, as they can at least see how much progress is being made!

Unfortunately, Excel crashes after 160 rows have been populated every time, and continues populating the sheet without scrolling.

I know this might sound like a silly request, but if anyone can give me any tips on how to programmatically scroll whilst populating a spreadsheet without crashing, it would be much appreciated.

Regards,

Rowan

[Norie]

Rowan

How are you populating the rows?

Does it really take that long that users will become bored waiting?

[RowanB]

I'm using visual basic to do some fairly time-consuming calculations on each row. The whole sheet can take around 5 minutes to populate. It's just a nice touch if the user can see how much progress is being made.

[Norie]

Rowan

Scrolling down row by row could be slowing down your code.

How about using something simple like the status bar to indicate progress?

Not exactly exciting I know, but it might work.

You could also look into using a userform with a progress bar, but that could also slow things down.

PS How is Excel 'crashing'?

[RowanB]

Yeah, I know it's slowing it down! haha Thanks for the status bar suggestion, It just says "not responding".

[Norie]

So how did you use the suggestion?

Did you add code to set the text in the status bar?

Please Login or Register  to view this content.

You can reset the status bar like this.

Please Login or Register  to view this content.

[RowanB]

Ah, that's a really cool idea Norie. Thank youy very much. I'll use that. I haven't tried a status bar yet.

[RowanB]

I've just found out that I get exactly the same problem using the status bar. Excel says "not responding" after processing 77 rows and the status bar stops.