Long code takes an age to run

**E3iron** · 08-25-2009, 06:30 AM

Hi

I have the following code: Which when its run it looks in AC for a value i.e RDA Uk and when it finds it it copies the row and then pastes in "UK". It will continue until it finds "", then it will move on to the next value and do the same again. I have 6/7 values to find and move to another sheet.

Can anyone help edit to code to help the macro to run quicker?

Please Login or Register  to view this content.

**royUK** · 08-25-2009, 06:47 AM

One of the main problems is that you are selecting ranges, this is not necessary.
You are alos using a loop to find the next blank cell, again time consuming.
You are using a loop to copy individual rows, it would be more efficient to copy a block of rows.

Attach an example workbook

**E3iron** · 08-25-2009, 07:06 AM

Getting a error when i'm trying to up load, i will try again soon

**royUK** · 08-25-2009, 08:06 AM

The file may be to big, try zipping it

**E3iron** · 08-25-2009, 08:49 AM

I get the following:
Database error
The Excel Help Forum database has encountered a problem.

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

Please try the following:
Load the page again by clicking the Refresh button in your web browser.
Open the www.excelforum.com home page, then try to open another page.
Click the Back button to try another link.

The www.excelforum.com forum technical staff have been notified of the error, though you may contact them if the problem persists.

We apologise for any inconvenience

**E3iron** · 10-05-2009, 05:04 AM

Please find sample work book attached

**DonkeyOte** · 10-05-2009, 07:15 AM

Based on your file perhaps try something along the lines of the below, note however I created a sheet ROM for Romania data as this was not present originally.

Please Login or Register  to view this content.

**E3iron** · 10-05-2009, 07:27 AM

Thanks very much for your help.

The reason there is no Romaina is because it goes in with the CE Region. Also this code works for all apart from UK.

**DonkeyOte** · 10-05-2009, 07:38 AM

The reason there is no Romaina is because it goes in with the CE Region.

OK, so change the associated sheet in vSheets from ROM to CE.
(you could store a listing of regions and appropriate sheet in a table somewhere if preferred)

Also this code works for all apart from UK.

Worked for me in the sample file.... though I should have added the code is based on the assumption that the various regions (RDA UK etc) are grouped together on Combined (ie not broken up RDA UK, RDA Benelux, RDA UK etc...) ... if not it would be worth switching to a Filter based iteration.