Scan for combo then insert row
 

I have a worksheet sorted by Columns D, E, and G. I am trying to create a
macro that, starting in row 3, will scan every row in Columns D, E, and G for
the following combination

Column D = Sale
Column E = Purchaser
Column G = Solution

When I find the last record of this combination (Column D/Sale), (Column
E/Purchaser), (Column G/Solution) I would like to insert a row below the last
combination. Can you help me?

Scan for combo then insert row
 

Try the following.

Sub ScanCombinations()
Dim i As Long
Dim lngRow As Long

With Sheets("Sheet1")
lngRow = .Cells(.Rows.Count, "D") _
.End(xlUp).Row
End With

For i = lngRow To 3 Step -1
If Cells(i, "D") = "Sale" And _
Cells(i, "E") = "Purchaser" And _
Cells(i, "G") = "Solution" Then
Rows(i + 1).Insert
Exit Sub
End If
Next i

End Sub

--
Regards,

OssieMac


"JHopper" wrote:

I have a worksheet sorted by Columns D, E, and G. I am trying to create a
macro that, starting in row 3, will scan every row in Columns D, E, and G for
the following combination

Column D = Sale
Column E = Purchaser
Column G = Solution

When I find the last record of this combination (Column D/Sale), (Column
E/Purchaser), (Column G/Solution) I would like to insert a row below the last
combination. Can you help me?

Scan for combo then insert row
 

My previous code will work if it is the active sheet and also I forgot to
turn off CutCopyMode which will give problems if you happen to have copied
something just before running the code.

Use the following instead.

Sub ScanCombinations()
Dim i As Long
Dim lngRow As Long

Application.CutCopyMode = False

'Replace "Sheet1" with your sheet name
With Sheets("Sheet1")
lngRow = .Cells(.Rows.Count, "D") _
.End(xlUp).Row

For i = lngRow To 3 Step -1
If .Cells(i, "D") = "Sale" And _
.Cells(i, "E") = "Purchaser" And _
.Cells(i, "G") = "Solution" Then
.Rows(i + 1).Insert
Exit Sub
End If
Next i

End With
--
Regards,

OssieMac


"JHopper" wrote:

I have a worksheet sorted by Columns D, E, and G. I am trying to create a
macro that, starting in row 3, will scan every row in Columns D, E, and G for
the following combination

Column D = Sale
Column E = Purchaser
Column G = Solution

When I find the last record of this combination (Column D/Sale), (Column
E/Purchaser), (Column G/Solution) I would like to insert a row below the last
combination. Can you help me?

Scan for combo then insert row
 

That's it; you nailed it! Thanks for the help OssieMac. I appreciate it.

"OssieMac" wrote:

My previous code will work if it is the active sheet and also I forgot to
turn off CutCopyMode which will give problems if you happen to have copied
something just before running the code.

Use the following instead.

Sub ScanCombinations()
Dim i As Long
Dim lngRow As Long

Application.CutCopyMode = False

'Replace "Sheet1" with your sheet name
With Sheets("Sheet1")
lngRow = .Cells(.Rows.Count, "D") _
.End(xlUp).Row

For i = lngRow To 3 Step -1
If .Cells(i, "D") = "Sale" And _
.Cells(i, "E") = "Purchaser" And _
.Cells(i, "G") = "Solution" Then
.Rows(i + 1).Insert
Exit Sub
End If
Next i

End With
--
Regards,

OssieMac


"JHopper" wrote:

I have a worksheet sorted by Columns D, E, and G. I am trying to create a
macro that, starting in row 3, will scan every row in Columns D, E, and G for
the following combination

Column D = Sale
Column E = Purchaser
Column G = Solution

When I find the last record of this combination (Column D/Sale), (Column
E/Purchaser), (Column G/Solution) I would like to insert a row below the last
combination. Can you help me?