Find Last in Range & Insert
 

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?

Find Last in Range & Insert
 

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?

Find Last in Range & Insert
 

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

Find Last in Range & Insert
 

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?

Find Last in Range & Insert
 

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?