Excel VBA - Insert row above specific text and copy formats and formula

Question

I see there are similar questions to mine however I am unable to find a VBA which includes both of my queries. I am fairly new to VBA and am therefore struggling to combine two codes into a single code which:

Inserts a specified number of rows above a row containing the text "TTDASHINSERTROW" and copies formats and formula from the above row.

The first code I have inserts a number of rows and copies the formula from above but is based on an "Active Cell".

Sub insertRow()

Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
If Rng = "" Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
'need To know how many formulas To copy down.
'Assumesfrom A over To last entry In row.
k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 256).End(xlToLeft).Column
Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown

End Sub

The second code inserts one row based on a search for the text "TTDASHINSERTROW".

Sub insertRow()

  Dim c As Range
  For Each c In Range("A:A")
    If c.Value Like "*TTDASHINSERTROW*" Then
        c.Offset(1, 0).EntireRow.Insert
    End If
  Next c

End Sub

Any help in combining these into a single code which can insert a specified number of rows above the specified text and copies the formats and formula will be appreciated.

UPDATE

I have come up with the following code which allows the user to add a specified number of rows through a pop up window when running the macro. The code still requires an active cell and copies the formula from above that cell.

Sub InsertRow()

Dim d As Integer
d = Range("A:A").End(xlDown).Row
Dim c As Range
For i = d To 1 Step -1
If Cells(i, 1).Value Like "TTDASHINSERTROW" Then

Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
If Rng = "" Then Exit Sub

Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
'need To know how many formulas To copy down.
'Assumesfrom A over To last entry In row.

k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 256).End(xlToLeft).Column
Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown


End If
Next
End Sub

Instead of the second part of the code refering to the active cell is it possible for it to find the cell with "TTDASHINSERTROW" and copy the formula and formatting from above that row?

Unfortunately I don't have enough rep to attach a screenshot.

Welcome to Stackoverflow. I am having a deja-vu somewhere with your question... — bonCodigo, Mar 03 '15 at 07:50
@bonCodigo: Does that imply that person did not look well before asking in SO? — Amen Jlili, Mar 03 '15 at 08:25
@JLILIAman, Bingo! However, I will hesitate to exercise strict *voting rights* given the details that `1.` OP has posted a code that he/she tried out, `2.` indicated `I see there are similar questions to mine however I am unable to find...` `3.` and this is his/her first post in SO. — bonCodigo, Mar 03 '15 at 08:56
I've searched. Really can't find anything that doesnt reference an active cell. I've tried creating it myself with no luck. — Justin, Mar 03 '15 at 08:56
@Justin, What if you have that particular string in number of adjacent columns and rows? Does it mean you want the function to insert rows up, down, right and left? — bonCodigo, Mar 03 '15 at 08:59
To honest with you @bonCodigo, if SO policies were enforced with no remorse, no one would be able to ask. The answer is always somewhere. But then again, SO is about helping people. — Amen Jlili, Mar 03 '15 at 09:01
@bonCodigo. I'm not too sure if I understand your question correctly. I have the "TTDASHINSERTROW" in a colum to give the VBA a reference point. All I need to do is add a row above that reference point. I previously had a vba which referenced a cell (I.e. A1) however changes to the document meant that the cell reference became inaccurate. — Justin, Mar 03 '15 at 09:03
@Justin: Welcome to StackOverFlow! Do not hesitate to ask any questions on SO. That being said, a little homework would not harm. The answer or *elements of the answer* may be found in SO if you look hard enough. :) — Amen Jlili, Mar 03 '15 at 09:04
@JLILIAman. I thought I had looked hard enough without any results hence my post. If you have a link which will point me in the right direction I'd appreciate the advice. Being new to VBA, the active cell and text search/reference is confusing the issue. — Justin, Mar 03 '15 at 09:10
@Justin if it's just one column, that solves half of the programme-control issues. You already have a code written to insert rows. But you need to copy the formula's row above before you do the insertion. Check here for a more robust [copy, insert function](http://stackoverflow.com/questions/21455635/excel-vba-macro-copy-and-insert-copied-cells). Don't forget to upvote it it helps. — bonCodigo, Mar 03 '15 at 09:12
@bonCodigo. Unfortuneatley that's not it. I need the functionality of my first code which allows users to select how many rows they want to insert but this code also requires the user to first select a cell where they want the row to be inserted above. I cannot have this as my sheet is to be used by multiple users who could then insert rows accidently in the wrong locations, messing with my formatting, hence why I need the insert function to reference the specific text (which the second code does). — Justin, Mar 03 '15 at 09:24
You can customize the code, so that you don't allow user to select where they want to insert rows. And isn't there too much human/manual intervention? And what does this mean? `Hence why I need the insert function to reference the specific text` - who adds this specific text to the sheet in the first place? where does that come from? Why don't you show us some sample data of your sheet. Like original data area and how the outcome should be. Perhaps you may attach a screen shot to the question, if your reps suffice. — bonCodigo, Mar 03 '15 at 11:51
@bonCodigo. See updates. By referenicing the cell that contains "TTDASHINSERTCELL" it takes out the human factor. I simply chose that as the reference as the original code included a reference as such which is unique and unlikely to be typed in anywhere by a user. Maybe you can assist me in providing sample data as my reps don't allow me to add a screen shot? The update to the original question is almost correct however just needs adjustment to change the "ActiveCell" to be a reference to whatever cells contains "TTDASHINSERTROW". I hope that helps you? — Justin, Mar 03 '15 at 15:45

score 0 · Answer 1 · answered Mar 03 '15 at 13:07

0

Sub insertRow()
Dim Rng As Long
Rng = InputBox("Enter number of rows required.")
If Rng = 0 Then Exit Sub
Application.ScreenUpdating = False 'this is unnecessary unless you often get seizures
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'tells the number of rows used
LastColumn = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'tells the number of columns used

  For i = 1 To LastRow 'for each row
    If Cells(i, 1).Value Like "*TTDASHINSERTROW*" Then 'if Range("A"&i) is like your string
        For j = 1 To Rng
            Rows(i).EntireRow.Insert
            Range(Cells(i, 1), Cells(i + 1, LastColumn)).FillUp
        Next
    End If
  Next

Application.ScreenUpdating = True
End Sub

answered Mar 03 '15 at 13:07

user3819867

1,114
1
8
18

1

This code adds 48 new rows for every 1 row entered in the application box. (I.e Input 1 = 48 new rows, Input 2 = 96 new rows, etc.). The update to the original question is almost correct however just needs adjustment to change the "ActiveCell" to be reference to whatever cells contains "TTDASHINSERTROW" – Justin Mar 03 '15 at 15:37
I assume there are 48 instances of the "*TTDASHINSERTROW*" in your file. Your solution first finds it then asks for the number, mine takes the input then does the same process for all instances. You forgot to detail your specification, it's GIGO. The correct sub for your case after specification would be adding 1. allow screen update 2. select Cells(i,1) 3. ask for the Rng 4. disallow screen update before the `For j =`... line. – user3819867 Mar 05 '15 at 08:25

score 0 · Accepted Answer · answered Mar 04 '15 at 05:10

0

Solved.

All I needed to do with my code is include a "find" function which located the cell containing "TTDASHINSERTROW", therefore making that cell the active cell.

Sub InsertRow()


Cells.Find(What:="TTDASHINSERTROW", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Dim d As Integer
d = Range("A:A").End(xlDown).Row
Dim c As Range
For i = d To 1 Step -1
If Cells(i, 1).Value Like "TTDASHINSERTROW" Then

Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
If Rng = "" Then Exit Sub

Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
'need To know how many formulas To copy down.
'Assumesfrom A over To last entry In row.

k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 256).End(xlToLeft).Column
Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown


End If
Next
End Sub

Thanks to everyone for the help on this!

answered Mar 04 '15 at 05:10

Justin

11
1
4

"Insert row above" is the new "Insert row below." Make sure your string contains wildcards as intended, you've changed that one. – user3819867 Mar 04 '15 at 07:52
@user3819867 Not sure I follow what you are saying? I'm not familiar with wildcards. As I have it the code works but should you have found an error which I'm not picking up I'd like to understand your comment. – Justin Mar 05 '15 at 07:57
First: if you do the evaluation for each of the occurrences you might want to use something along the lines of `Resume Next` in lieu of `Exit Sub` which completely drops your process instead of going to the next string. The "*" in your string implies that there can be any number of characters at that given place, e.g. "NDKJ**TTDASHINSERTROW**NSGRJL". – user3819867 Mar 05 '15 at 08:38
@user3819867 Thanks for clearing that up for me. Will mark this as the correct answer now and close out the query. – Justin Mar 05 '15 at 10:29

Excel VBA - Insert row above specific text and copy formats and formula

2 Answers2

Linked