Insert Rows, based on values in a column, for a 300,000 row dataset

**engineerlady** · 02-02-2013, 07:35 PM

Hi,
This is what I'm trying to do:
1. Check the values in column A, if they are equal, AND
2. The sequential order of values in column B is either 6 then 7, or 2 then 6
3. THEN insert a row, AND
4. Enter the same column A value, AND the value "3" in column B

Please see attached spreadsheet.

Anthony from this forum has written a code (see macro in the spreadsheet) that works great, but only for a data set up to 15,000 rows. When I try to use it on my data set that has over 300,000 rows, it crashes. Is there a way to change the code to handle a large number of rows?

Thank you.

**stanleydgromjr** · 02-03-2013, 12:05 AM

engineerlady,

Thanks for the workbook.

I expanded the data in Sheet1 down to row 10,401.

Your original macro and function took 30.766 seconds.

The below macro took 14.047 seconds.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Find6 macro.

**stanleydgromjr** · 02-03-2013, 12:23 AM

engineerlady,

I missed this part "2. The sequential order of values in column B is either 6 then 7, or 2 then 6"

Be back later.

**stanleydgromjr** · 02-03-2013, 12:27 AM

engineerlady,

Thanks for the workbook.

I expanded the data in Sheet1 down to row 10,401.

Your original macro and function took 30.766 seconds.

The below new macro took 9.922 seconds.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Find6V2 macro.