"Not Responding" while code is running... need help with optimizing my code please!

Hi VBA gurus,

When i run my code, the application status shows the count but stops after a short while and it just turns to "Not Responding". Could it be that my code is inefficient? I'm not an expert at vba as its all mostly self-taught. please someone help!!!

Alittle background:

1. the code is found on a master workbook (which i leave open in the background), I then open a separate workbook each time to run the macro.
2. the data starts from row 4 in each separate workbook.
3. for each row of data found in a separate workbook- 2 points are used to trigger a match in the "ref" sheet of the master workbook. Namely: the active workbook's name and one other data points (sort of like a sumproduct/ 2 intersection)
4. once the match is found, other data from the "ref" sheet is then copied over to the active workbook. and a string is introduced to another cell in the row as well

Is there a more efficient way to do this??

btw, i am running close to 25000 rows of data in one instance. and i have to do this for 15~20 separate workbooks

Please Login or Register  to view this content.

attach please a sample file for testing

Hi, dexcee,

if we knew what you are comparing we could give a better input on how to speed it up. I took the statusbar out. The roaming through the cells could be shortened by a Find-command so that one of the loops should become faster (or use a worksheetfunction.-Countif to find out if and how much hits will be there and exit the for after the according number fo hits).

Please Login or Register  to view this content.

Ciao,
Holger

Hi, dexcee,

you could use the Autofilter to narrow down the range in which to work in the ActiveWorkbook on Column E, check if there are any data sets being available by checking the last visible row (lngLast) to be equal to or greater than 4 and then run a For Each rngCell in wsAct.Range("E4:E" & lngLast).SpecialCells(xlCellTypeVisible) and compare that row to the other requirements instead of looping through the entire range for each item.

Ciao,
Holger

Hi, dexcee,

filktering prior to running your code? That would narrow the visible number of rows down but not be any helpful.

The loops should be restructured and the filter set on the value of wsRef.Cells(y, 16). If possible you could sort the reference sheet as well and only change the Autofilter in the ActiveSheet if a new value is found. This will cut down the number of actions to take dramatically.

Ciao,
Holger