Check value in a column, if condition is met, insert a row

[engineerlady]

Hi,

I need help on a macro that will:
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 the attached spreadhseet for a visual explanation.

I need a macro to perform this process for hundreds of thousands of row. I would greatly appreciate any help.

Thank you!

[smuzoen]

You could use the following - See attached workbook

Please Login or Register  to view this content.

[engineerlady]

Hi Anthony,
First, I just want to give you a big thanks for helping me with this task!

I opened your attached spreadsheet and tried to run the macro, but it gets hung up on:

Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range

Is there a way to make this work?

Thank you!

[smuzoen]

I am not sure why you are having problems with the code - does it work in the workbook I uploaded? Anyway attached is a different function to create a collection of ranges containing the combinations you require. See how this works for you

[engineerlady]

It works perfectly Anthony!! Thank you so much!!!! You're awesome!

[engineerlady]

Hi,

Both of these previously submitted codes geniously done by Anthony work great...on less than 20,000 rows of data. The problem is I have over 300,000 rows of data, and probably need to use an array to manipulate the data. Is there a way to code the program so that it can handle a large amount of data?

Thank you.

[smuzoen]

Is the problem that the code creates an error is is the problem that the processing time is too long. I would have thought that despite the number of rows you are working with that the code should cope however can you explain what the problem with the code is - it errors out/too long to run/???? - please describe the problem