how do paste same data in every other line
Afternoon all
I am looking at a data table comprising 1 column x multiple rows. I would like to be able to insert between every entry (all different) the sequence of: DATA blank row, cell containing 'S' cell containing 'E' NEXT DATA. I have seen some threads and can insert three blank rows but am falling down at getting the specific texts in the cells. Any help would be appreciated. |
how do paste same data in every other line
Hi,
If I've understood correctly, right click the sheet tab, view code and paste this in and run it. Sub stance() lastrow = Range("A65536").End(xlUp).Row For x = lastrow To 2 Step -1 Rows(x & ":" & x).Select For y = 1 To 3 Selection.Insert Shift:=xlDown Next Cells(x, 1).Select ActiveCell.Offset(1, 0).Value = "S" ActiveCell.Offset(2, 0).Value = "E" Next End Sub Mike "ben0209" wrote: Afternoon all I am looking at a data table comprising 1 column x multiple rows. I would like to be able to insert between every entry (all different) the sequence of: DATA blank row, cell containing 'S' cell containing 'E' NEXT DATA. I have seen some threads and can insert three blank rows but am falling down at getting the specific texts in the cells. Any help would be appreciated. |
how do paste same data in every other line
Hi Ben,
Assuming your data in column A Set B values like this B2: blank B3: S B4: E B5: =INDIRECT("A"&(ROW()-1)/4+1) Highlight B2 to B5 Grab the fill handle and drag way way way down your sheet, it will start returning zeroes after row (4 times your last column A row) Not very elegant and a bit of tidy up to do, but it works. HTH Martin "ben0209" wrote in message ... Afternoon all I am looking at a data table comprising 1 column x multiple rows. I would like to be able to insert between every entry (all different) the sequence of: DATA blank row, cell containing 'S' cell containing 'E' NEXT DATA. I have seen some threads and can insert three blank rows but am falling down at getting the specific texts in the cells. Any help would be appreciated. |
how do paste same data in every other line
This macro should do what you want...
Sub InsertData() Dim X As Long Dim EndRow As Long Dim NewEndRow As Long Const ColNum As Long = 1 Const StartRow As Long = 2 Const NewDataLineCount As Long = 3 EndRow = ActiveSheet.Cells(Rows.Count, ColNum).End(xlUp).Row NewEndRow = (EndRow - StartRow) * (NewDataLineCount + 1) + StartRow For X = EndRow To StartRow Step -1 With Cells(NewEndRow - (NewDataLineCount + 1) * (EndRow - X), ColNum) .Value = Cells(X, ColNum).Value If X < EndRow Then .Offset(1, 0).Value = "" .Offset(2, 0).Value = "S" .Offset(3, 0).Value = "E" End If End With Next End Sub Set the column number where your data is located (assumed to be 1 for this example) in the ColNum constant, the starting row for your data (assumed to be 2 for this example) in the StartRow constant, the number of lines of new data you will be inserting in the NewDataLineCount constant (3 as per your posting) and set the new lines of data to be inserted in the If-Then block of code via the Offset properties as shown. The code is general and can be changed as needed by setting the three constants just mentioned and setting the proper number of Offset statements to match the data to be inserted. Rick "ben0209" wrote in message ... Afternoon all I am looking at a data table comprising 1 column x multiple rows. I would like to be able to insert between every entry (all different) the sequence of: DATA blank row, cell containing 'S' cell containing 'E' NEXT DATA. I have seen some threads and can insert three blank rows but am falling down at getting the specific texts in the cells. Any help would be appreciated. |
All times are GMT +1. The time now is 04:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com