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.
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.
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
FindandInsertmethods ofRangeobjects. Try them out. If needed, post a focused question about any issues that you encounter when you use these methods in your code.