CODEWITHSUNDEEP
excelvba
LizAAR
LizAAR

Reputation: 1

Insert row after specific text is found

I am fairly new to excel and I am trying to setup a macro that adds a row after a specific point of the worksheet. Example: Row 2 contains text "Original", so it should insert a new row afterwards, and so on.

I know that it might be easier to insert before something, so I could change the setup (so for example the word "original" would be in row 2 and the new row is added above it) if that is an easier solution.

Is this possible? How?

Thanks for all the possible help.

Upvotes: 0

Views: 10935

Answers (2)

Chris Slade
Chris Slade

Reputation: 309

Slightly simpler than the previous answer:

Sub NewRowInsert()
Dim SearchText As String
Dim GCell As Range

SearchText = "Original"
Set GCell = Cells.Find(SearchText).Offset(1)
GCell.EntireRow.Insert

End Sub

This will work with the current active sheet. If you want to use some other sheet, say Sheet2, you could use:

Set GCell = Worksheets("Sheet2").Cells.Find(SearchText).Offset(1)

and if you wanted to operate on a different workbook e.g. TestBook.xlsx, you could use:

Set GCell = Workbooks("TestBook.xlsx").Worksheets("Sheet2").Cells.Find(SearchText).Offset(1)

Note that I've avoided the use of select. This may not be an issue for you, but if you're searching through thousands of rows and making many replacements it could speed up your code considerably.

Upvotes: 2

Tom36
Tom36

Reputation: 152

Try the code below = you can change the textvalue variable value to what ever you want to search for.

Sub newRow()
Dim textvalue As String

textvalue = "Original"

Cells.Find(What:=textvalue, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.offset(rowOffset:=1, columnOffset:=0).Activate

ActiveCell.EntireRow.Insert

End Sub

Upvotes: 0

Related Questions