VBA Code running very slow to seperate 250 rows into 5 categories

The basics of this code are to examine a spreadsheet by multiple criteria and separate it into 5 categories. Then copy 4 of the categories and only certain column of data to another spreadsheet sorting it into 4 segregated areas on the spreadsheet, inserting rows as needed. It runs as it is but takes up to a minute to make it through the 250 rows and is inserting multiple blank rows and over righting other rows. What am I missing please

Please Login or Register  to view this content.

Hi,

Please edit your post and use code tags as required by our http://www.excelforum.com/forum-rule...rum-rules.html it really improves readability

Consider also posting sample workbook to play with.

Just at the first glance - there are many

Please Login or Register  to view this content.

commands - they can be quite time-consumming, especially if there is a lot of data below/above the range.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

What should we do in this form before clicking the make ready button, to duplicate your issue?

You have a very busy workbook. You don't explain where to find the code in your first post. I found it because I did a search for the name of the procedure.

You have a lot of stuff in this workbook and to just say push a button and you will see a jumbled mess doesn't help me.
If we are talking about the layout form the only way I know to run it is by bringing it up in the VBE and choosing F5, then clicking the make ready button and then the form disappears, so whatever it is I'm supposed to see that's a garbled mess isn't apparent to me.

Okay so I open the workbook, I push the start button, the form opens I push make ready button the form closes and I'm not sure what I'm supposed to be seeing that's a garbled mess.

That's the kind of explanation I'm looking for. I'd be happy to take a look at it. I'm busy right now, so give me some time.

You have a seriously complicated worksheet here.
I'm trying to step through your F row loop code to see what's going on, but this function.

Please Login or Register  to view this content.

keeps getting triggered and then my Excel crashes. If you wrote this code and these API functions yourself I would think you are more than capable of stepping through this code and seeing what the values are and why it's doing what it's doing. If this function is triggering on every line that's probably a good clue as to why your code is so slow, as to what's going on with your other sheet I would suggest stepping through line by line and seeing what is happening.

MR.Cells(MNoticeMain, 1).Offset(-1).EntireRow.Insert" it offsets -2 once and -1 the next

It's adding one row each time and it adds the row offset -1 from the current value of MNoticeMain. You have eight lines that insert rows. I put a break point on each line just to make sure it was stopping and I ran through the loop line by line several times. MNoticeMain was always 25 each time it ran and it always inserted a row above. When you insert a row above, what happens is the row appears to be pushed down. I noticed that only one of the several times that I ran the code line by line did any data appear. The data was below the blue line labeled 2 bedroom and the data was 1614F 2X1 Up 9/14/2001 Not Ready, this data appeared on one of the first times I ran the code and then the code just kept adding rows and I didn't see any data being filled in.

Skywriter that was the kick in the teeth I needed. The row keys I am using (MNoticeMain is one of them) change location every time the loop runs and a row gets added. I had placed the keys outside the loop which was causing them to find their position once and the loop to keep over writing on itself. I moved them into the loop so that they get rechecked each time the loop runs and it works great now, Thank you.

You're welcome.

Please click the Thread Tools drop down box above your first post and choose solved.