Long shot? Copy rows but have XL2K insert a space between each row??

[StargateFanFromWork]

If I have this type of thing in a workbook:

Information in row A
Information in row B
Information in row C
Information in row D




Is there a way to have Excel add an extra line to what we copy to the
clipboard so that when we paste the data, we get this instead of the above?:

Information in row A

Information in row B

Information in row C

Information in row D


It feels like a longshot but so many times I've been surprised at what Excel
can do by what you've all shown me in this group, that I'm putting the
message out there anyway.

Thanks much! D

 

[Gord Dibben]

Rows are the numbers down side of sheet.

Columns are the letters across top of sheet.

Assuming you mean rows....................

Sub InsertRows22()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")

r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 1
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[NickHK]

If the paste is outside of Excel, then use the DataObject; requires
reference to Forms2. Something like:

Private Sub CommandButton2_Click()
Dim DatObj As DataObject

Const FORMAT_TEXT As Long = 1

Set DatObj = New DataObject

Selection.Copy

With DatObj
.GetFromClipboard
If .GetFormat(FORMAT_TEXT) = True Then
.SetText Replace(.GetText(FORMAT_TEXT), vbNewLine, vbNewLine &
vbNewLine)
.PutInClipboard
End If

End With

End Sub

NickHK