If partial string = "dup" is in Column A, insert a row below that cell.

[Niedermee]

Example with Expected Outcome.xlsxExample with Expected Outcome.xlsxI'm trying to piece a bit of code together from different solutions I've come across. What I have runs but doesn't insert rows. What did I do wrong?

Please Login or Register  to view this content.

Once I get this worked out, I'm trying to piece the code together that will find the max in Col. B from the two rows above the new row, and place the value in the new row, Col. B. Additionally, I want it to concatenate the original text containing the "dup" with "_max" in Col. A of the new row. I can do this manually, but there are ~50 of these "dup" rows.

[p24leclerc]

try this:

Please Login or Register  to view this content.

[Leith Ross]

Hello Niedermee,

Here is another method. The assumptions are column "C" is empty, the row below the last row with data is empty, and the data is in ascending order as shown.

Please Login or Register  to view this content.

[Niedermee]

Thanks, Pierre. It works, but only if I use the data just as presented. My real data has many more columns than just the two I provided in the example dataset and it gives me an error '13', Type Mismatch.
When I click on debug the following code is highlighted yellow:

Then M_ax = ActiveCell.Offset(i, 1)

-P.

[event21]

Hi -

Also try this one;

Please Login or Register  to view this content.

event

[Niedermee]

If I have spaces in my dataset, meaning blank rows between groups, will this cause problems?

-P.

[p24leclerc]

to Event21,
something is wrong in your MAX function.
WorksheetFunction.Max(r.Offset(-1, 1).Resize(2))

Resize and Offset should be variable too.

This modification seems to work:

Please Login or Register  to view this content.

I really learned something with your code Event21. I was looking for something like this, but it seems to be out of reach for me.
Thanks

[event21]

to Event21,
something is wrong in your MAX function.
WorksheetFunction.Max(r.Offset(-1, 1).Resize(2))

Resize and Offset should be variable too.

This modification seems to work:

Please Login or Register  to view this content.

I really learned something with your code Event21. I was looking for something like this, but it seems to be out of reach for me.
Thanks

so if you run your modified code, you will get;

Please Login or Register  to view this content.

the sample from -P. on this red portion = MW-04-dup 25

If I have spaces in my dataset, meaning blank rows between groups, will this cause problems?

no as long as the space row is not covered by the two cells above the "dup" cell

let us see the test results from -P.

event

[p24leclerc]

Sorry, I did not properly read the OP requirements. Event21 ,your solution is right what the OP needs.
The code below could be used if someone wants the maximum of all the above rows.

Please Login or Register  to view this content.

[Niedermee]

Thanks everyone for the help. Seems the code works. What is the best way to learn the basic definitions of the language used in VBA? Is there a public dictionary that defines all these "commands"?