If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Script to insert Row
How would I write a scrtip to insert a row under the number in A that holds a
"NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in it and paste it in the row that was inserted underneith. When it copies the row it will copy the whole row except what is in A. Example Before A B 1 MT 12 2 D 14 3 x 15 Example After 1 2 1 MT 12 2 12 3 D 14 4 X 15 Thanks |
#2
|
|||
|
|||
Script to insert Row
Sub CopyInsert()
'Find the last cell in column A With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For i = LastRow To 1 Step -1 If Cells(i, 1).Value = "NS" Or Cells(i, 1).Value = "MT" Then 'Copy row Cells(i, 1).EntireRow.Copy 'Insert new row Cells(i + 1, 1).Insert Shift:=xlDown 'Clear the inserted row's A cell Cells(i + 1, 1).ClearContents End If Next End Sub -- Best Regards, Luke M "Jeremy" wrote in message ... How would I write a scrtip to insert a row under the number in A that holds a "NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in it and paste it in the row that was inserted underneith. When it copies the row it will copy the whole row except what is in A. Example Before A B 1 MT 12 2 D 14 3 x 15 Example After 1 2 1 MT 12 2 12 3 D 14 4 X 15 Thanks |
#3
|
|||
|
|||
Script to insert Row
Jeremy
try this Sub Insert_Copy() Set Sht = Sheets("Sheet1") 'Change to suit Dim x As Long MyColumn = "A" For x = Sht.Cells(Rows.Count, MyColumn).End(xlUp).Row To 1 Step -1 If UCase(Sht.Cells(x, MyColumn)) = "MT" _ Or UCase(Sht.Cells(x, MyColumn)) = "MS" Then Sht.Rows(x + 1).insert Sht.Rows(x).Copy Destination:=Cells(x + 1, 1) Sht.Cells(x + 1, 1).ClearContents End If Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jeremy" wrote: How would I write a scrtip to insert a row under the number in A that holds a "NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in it and paste it in the row that was inserted underneith. When it copies the row it will copy the whole row except what is in A. Example Before A B 1 MT 12 2 D 14 3 x 15 Example After 1 2 1 MT 12 2 12 3 D 14 4 X 15 Thanks |
Thread Tools | |
Display Modes | |
|
|