Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace and Find & Insert macro help needed | Excel Programming | |||
find...insert | Excel Programming | |||
Find RangeCopyInsert into Sheet | Excel Programming | |||
Find and insert? | Excel Programming | |||
How do I find a value and insert new value | Excel Worksheet Functions |