I want to find the last "3" in column A after row 65 and insert a row below
this and make this the active row for pasting formula and formatting from
named range "Engineering"

Could someone please help?

Try the below procedure.

Sub FindLast3()
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Sheet1")
Dim C As Long
For C = 65535 To 65 Step -1 'Won't work if there is a 3 in row 65536.
If WS.Cells(C, 1).Value = 3 Then
WS.Cells(C + 1, 1).Rows.Insert
WS.Cells(C + 1, 1).Activate
Exit Sub
End If
Next C
End Sub

Set WS to equal whatever your worksheet is.

Hope this helps.

Bill Horton

"Karen McKenzie" wrote:

I want to find the last "3" in column A after row 65 and insert a row below
this and make this the active row for pasting formula and formatting from
named range "Engineering"

Could someone please help?

Thanks William,

When I run it it seems to insert cells into column A only, it doesn't insert
a complete row.

"William Horton" wrote:

Try the below procedure.

Sub FindLast3()
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Sheet1")
Dim C As Long
For C = 65535 To 65 Step -1 'Won't work if there is a 3 in row 65536.
If WS.Cells(C, 1).Value = 3 Then
WS.Cells(C + 1, 1).Rows.Insert
WS.Cells(C + 1, 1).Activate
Exit Sub
End If
Next C
End Sub

Set WS to equal whatever your worksheet is.

Hope this helps.

Bill Horton

"Karen McKenzie" wrote:

I want to find the last "3" in column A after row 65 and insert a row below
this and make this the active row for pasting formula and formatting from
named range "Engineering"

Could someone please help?

Karen,

Change the WS.Cells(C + 1, 1).Rows.Insert line to...
WS.Cells(C + 1, 1).Entirerow.insert

That should do it.

Bill Horton

"Karen McKenzie" wrote:

Thanks William,

When I run it it seems to insert cells into column A only, it doesn't insert
a complete row.

"William Horton" wrote:

Try the below procedure.

Sub FindLast3()
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Sheet1")
Dim C As Long
For C = 65535 To 65 Step -1 'Won't work if there is a 3 in row 65536.
If WS.Cells(C, 1).Value = 3 Then
WS.Cells(C + 1, 1).Rows.Insert
WS.Cells(C + 1, 1).Activate
Exit Sub
End If
Next C
End Sub

Set WS to equal whatever your worksheet is.

Hope this helps.

Bill Horton

"Karen McKenzie" wrote:

I want to find the last "3" in column A after row 65 and insert a row below
this and make this the active row for pasting formula and formatting from
named range "Engineering"

Could someone please help?

Sub karen()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 66 Step -1
If Cells(i, 1).Value = 3 Then
Cells(i, 1).Offset(1, 0).EntireRow.Insert
Cells(i, 1).Offset(1, 0).Select
Exit Sub
End If
Next
End Sub
--
Gary''s Student - gsnu200714