I have rows in a large spreadsheet marked with an "x" based on criteria in an
"if" statement. I would like to insert a row above those marked rows. Is
there an easy way to accomplish this?

Then if possible, I would like to copy the values/formulas from an existing
row into the new inserted rows. Is it possible to automate this?

Thanks in advance for any help.

This little sub will take into account that your search range will gro
as you insert rows. Let me know if it helps.

Sub FindXAndInsert()
Dim rSearch As Range
Dim rTarget As Range
Dim lRow As Long
Dim lRows As Long

' Assuming "SearchColumn" is a Named range
Set rSearch = Range("SearchColumn")

' Get number of Rows
lRows = rSearch.Rows.Count

' Loop through Rows
lRow = 1
Do While lRow <= lRows

' Set a range object
Set rTarget = rSearch.Range("A1").Offset(lRow - 1)

' Test for 'x'
If rTarget.Value = "x" Then
' Insert row above target
rTarget.EntireRow.Insert

' Mark inserted so you can see
rTarget.Offset(-1).Value = "Inserted From: "
rTarget.Address(False, False)

' Account for inserted row and the current target skippin
first
' If you don't skip the first row you just end up pushin
your selection down the sheet
If lRow 1 Then lRow = lRow + 1

' Bump row count up one to account for inserted ro
skipping first
If lRow 1 Then lRows = lRows + 1
End If

' Iterate Next Row
lRow = lRow + 1
Loop
End Su

--
tssop
-----------------------------------------------------------------------
tssopa's Profile: http://www.excelforum.com/member.php...fo&userid=1660
View this thread: http://www.excelforum.com/showthread.php?threadid=31465

This is exactly what I was looking for. It works great.

Thanks for your help.

"tssopa" wrote:


This little sub will take into account that your search range will grow
as you insert rows. Let me know if it helps.

Sub FindXAndInsert()
Dim rSearch As Range
Dim rTarget As Range
Dim lRow As Long
Dim lRows As Long

' Assuming "SearchColumn" is a Named range
Set rSearch = Range("SearchColumn")

' Get number of Rows
lRows = rSearch.Rows.Count

' Loop through Rows
lRow = 1
Do While lRow <= lRows

' Set a range object
Set rTarget = rSearch.Range("A1").Offset(lRow - 1)

' Test for 'x'
If rTarget.Value = "x" Then
' Insert row above target
rTarget.EntireRow.Insert

' Mark inserted so you can see
rTarget.Offset(-1).Value = "Inserted From: " &
rTarget.Address(False, False)

' Account for inserted row and the current target skipping
first
' If you don't skip the first row you just end up pushing
your selection down the sheet
If lRow 1 Then lRow = lRow + 1

' Bump row count up one to account for inserted row
skipping first
If lRow 1 Then lRows = lRows + 1
End If

' Iterate Next Row
lRow = lRow + 1
Loop
End Sub


--
tssopa
------------------------------------------------------------------------
tssopa's Profile: http://www.excelforum.com/member.php...o&userid=16605
View this thread: http://www.excelforum.com/showthread...hreadid=314655