Macro to Insert a Row in Excel

KitK369

New Member
Joined
Aug 27, 2018
Messages
14
I have been searching the internet and watching Youtube videos for days trying to create a macro in VBA that will enter a row above a specific text string in a worksheet in Excel. Every worksheet I would perform this macro on has the term "Actual Earnings" somewhere between cells A19 and A22. What I would like for the macro to do is find the Text "Actual Earnings" and insert rows above the text depending on where it falls in the spreadsheet. If it falls in cell A22, I don't want it to do anything. If it falls in A21, I want it to enter 1 row. If it falls in A20, I want it to enter two rows and if it falls into A19 I want it to enter 3. I'm trying to make each worksheet in my workbook uniform with the row "Actual Earnings" always falling in cell A22. Can anyone help me formulate this Macro? I found one that will enter a row but it enters the row above where I have selected a cell in the workbook as opposed to entering it above the text string and I'm not advanced enough to understand how to do everything I want it to. I would also like for it to work without a command button. Here is what I have to start with, I don't even know if it'll work for what I'm trying to do. Any help would be greatly appreciated!!

Sub InsertRow()


Dim Found As Range
Set Found = Columns("A").EntireRow.Find(what:="Actual:", LookIn:=xlValues, Lookat:=xlWhole)
If Not Found Is Nothing Then Found.Select
Rows(Selection.Row).Insert Shift:=xlDown


End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
When I tested the macro on some dummy sheets, it worked properly. I think that it would be easier to help and test possible solutions if I could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
I played around with it a bit and I was able to get the macro to work. The cell was merged between A22:G22 so I just needed to update the data to include through column G and it worked. Thanks again for the help! You're a lifesaver!
 
Upvote 0
You are very welcome. :) You should avoid merged cells if at all possible because they cause havoc for Excel macros. Do some research into "Centre across selection". It can most often achieve the same results as merging cells with the problems.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
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