Excel Macro to insert rows
 

Hello,

I have a work sheet in thich the parameters are arranged as per the
temp. I have to find the word 'temp 'and insert a complete row before
temp. Can I get some help tp write the excel macro for the same.

Excel Macro to insert rows
 

Try this one - click any cell in the column where the word "temp" is expected
to be then run the macro. As it is setup it ignores case, so "Temp" = "temp"
etc and it looks for "temp" anywhere in the cells, so "no temp" or "temp."
would be treated as "temp" and a row will be inserted. If 'temp' or the
exact phrase is all that should be in a cell, then change the part of the
..Find commands from
LookAt:=xlPart,
to
LookAt:=xlWhole,

Hope this helps at least a little.

Sub InsertRowAfterTemp()
Dim LastTempRowFound As Long

Selection.EntireColumn.Select
On Error Resume Next
Selection.Find(What:="temp", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If Err < 0 Then
Err.Clear
MsgBox "Phrase not found in this column."
ActiveCell.Select ' unselect the column
Exit Sub
End If
ActiveCell.Select ' at 1st row with a match
LastTempRowFound = ActiveCell.Row
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Application.ScreenUpdating = False ' speed things up
Do Until ActiveCell.Row <= LastTempRowFound
Cells.Find(What:="temp", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

Selection.EntireRow.Insert
If ActiveCell.Row LastTempRowFound Then
LastTempRowFound = ActiveCell.Row
End If
ActiveCell.Offset(1, 0).Activate
Loop
Application.ScreenUpdating = True

End Sub




"Dhawal" wrote:

Hello,

I have a work sheet in thich the parameters are arranged as per the
temp. I have to find the word 'temp 'and insert a complete row before
temp. Can I get some help tp write the excel macro for the same.

Excel Macro to insert rows
 

I appreciate it. That absolutely solves my problem.



JLatham (removethis) wrote:
Try this one - click any cell in the column where the word "temp" is expected
to be then run the macro. As it is setup it ignores case, so "Temp" = "temp"
etc and it looks for "temp" anywhere in the cells, so "no temp" or "temp."
would be treated as "temp" and a row will be inserted. If 'temp' or the
exact phrase is all that should be in a cell, then change the part of the
.Find commands from
LookAt:=xlPart,
to
LookAt:=xlWhole,

Hope this helps at least a little.

Sub InsertRowAfterTemp()
Dim LastTempRowFound As Long

Selection.EntireColumn.Select
On Error Resume Next
Selection.Find(What:="temp", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If Err < 0 Then
Err.Clear
MsgBox "Phrase not found in this column."
ActiveCell.Select ' unselect the column
Exit Sub
End If
ActiveCell.Select ' at 1st row with a match
LastTempRowFound = ActiveCell.Row
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Application.ScreenUpdating = False ' speed things up
Do Until ActiveCell.Row <= LastTempRowFound
Cells.Find(What:="temp", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

Selection.EntireRow.Insert
If ActiveCell.Row LastTempRowFound Then
LastTempRowFound = ActiveCell.Row
End If
ActiveCell.Offset(1, 0).Activate
Loop
Application.ScreenUpdating = True

End Sub




"Dhawal" wrote:

Hello,

I have a work sheet in thich the parameters are arranged as per the
temp. I have to find the word 'temp 'and insert a complete row before
temp. Can I get some help tp write the excel macro for the same.

Excel Macro to insert rows
 

Those are always good words to see. Thanks for letting me know that it was
effective. Glad to have been able to be of assistance.

"Dhawal" wrote:

I appreciate it. That absolutely solves my problem.



JLatham (removethis) wrote:
Try this one - click any cell in the column where the word "temp" is expected
to be then run the macro. As it is setup it ignores case, so "Temp" = "temp"
etc and it looks for "temp" anywhere in the cells, so "no temp" or "temp."
would be treated as "temp" and a row will be inserted. If 'temp' or the
exact phrase is all that should be in a cell, then change the part of the
.Find commands from
LookAt:=xlPart,
to
LookAt:=xlWhole,

Hope this helps at least a little.

Sub InsertRowAfterTemp()
Dim LastTempRowFound As Long

Selection.EntireColumn.Select
On Error Resume Next
Selection.Find(What:="temp", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If Err < 0 Then
Err.Clear
MsgBox "Phrase not found in this column."
ActiveCell.Select ' unselect the column
Exit Sub
End If
ActiveCell.Select ' at 1st row with a match
LastTempRowFound = ActiveCell.Row
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Application.ScreenUpdating = False ' speed things up
Do Until ActiveCell.Row <= LastTempRowFound
Cells.Find(What:="temp", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

Selection.EntireRow.Insert
If ActiveCell.Row LastTempRowFound Then
LastTempRowFound = ActiveCell.Row
End If
ActiveCell.Offset(1, 0).Activate
Loop
Application.ScreenUpdating = True

End Sub




"Dhawal" wrote:

Hello,

I have a work sheet in thich the parameters are arranged as per the
temp. I have to find the word 'temp 'and insert a complete row before
temp. Can I get some help tp write the excel macro for the same.