Insert a row (when cell in row above has a certain value) and then paste into that row

Welcome of Spring

New Member
Joined
Oct 31, 2022
Messages
1
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi MrExcel friends,

I need some help on two things. I would like to know how do I write a Macro for something that looks like the below.
The first thing I want to do is to to insert 2 rows below whenever I see a Mango (we will only ever see one Mango within each Country) and paste into that 2 whole rows (ROWA&B).
ROWA&B is the same thing each time, and the cells inside has been formulated appropriately. I think I will only need to instruct the macro to copy it once. It's just the 56th and 57th row of my sheet (not pictured). I would like it to paste the formatting as well.

The second thing I want to do is I would like to, within each Country, drag the row where the Fruit = Apple to a row below the Fruit = Strawberry row. (I am not sure if I need the Macro to recognise the country as a unique category - we could just ask the macro to search for the next available row which says strawberry. Since the next available row that says strawberry is definitely within the same country.?)

Appreciate if you could let me know what is the VBA code for this.

Screenshot 2022-11-01 at 12.06.07 PM.png
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top