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.

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.

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.

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.