Copy Insert Row At Start End Or Middle with different Columns same row range

[Kieranz]

Hello All
My Requirement:
To be able to insert a single row at the start of 'data' ie row9 or anywhere in the middle or at the end, above the "Last Row" with all its formats and formulas and bells.
29575

My attempt:
Inserting Row in the middle and at the end is working.
But inserting at the start row i.e. Row9, is where my problem is.
I am making some fundamental mistake or not understanding the Properties and Methods.



Sub m1Try1()
Dim sConC As String: sConC = "b1:h1,k1,p1"
Dim rCopy As Range
With ActiveCell
If .Row = 9 Then
.Offset(1, 0).EntireRow.Copy
.Offset(1, 0).EntireRow.Insert
Set rCopy = .Offset(0, -1).EntireRow.SpecialCells(xlCellTypeConstants).Address
.Offset(1, -1).Range(sConC) = .Offset(0, -1).Range(rCopy).Value2 ''.PasteSpecial (xlPasteValues)
.Offset(0, -1).Range(sConC).ClearContents
ElseIf .Row = 10 Then
Rows("10").Copy Rows("11")
.Offset(0, -1).Range(sConC).ClearContents
.Select
Else
.Offset(-1, 0).EntireRow.Copy
.Offset(-1, 0).EntireRow.Insert
.Offset(-1, -1).Range(sConC).ClearContents
End If
End With
Application.CutCopyMode = False
End Sub


With the two codes below I am trying to copy the constants on Row9 and paste them onto Row10 (pretending I have already made a duplicate of Row10 and then all I need to do is paste the constants into the new Row10 below) and finally clearing the constants in Row9.
This is to just see how the code is working but I am getting terribly knotted and bombed out!



Sub m1Try2() 'as range
Dim rCopy As Range
'activcecell is B9
Set rCopy = ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants)
'To test
Debug.Print rCopy.Address
ActiveCell.Offset(1, -1).EntireRow.Value2 = ActiveCell.Offset(0, -1).Range(sCopy).Value
'does not work :(
End Sub

Sub m1Try3() 'as string
Dim sCopy As String
'activcecell is B9
sCopy = ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).Address
'To test
Debug.Print sCopy
ActiveCell.Offset(1, -1).EntireRow.Value2 = ActiveCell.Offset(0, -1).Range(sCopy).Value
'does not work :(
End Sub


Many thanks for any assistance.
Rgds Kie

[p45cal]

ry:
Sub blah()
With ActiveCell.EntireRow
If .Row = 9 Then .Copy Else .Offset(-1).Copy
.Insert
On Error Resume Next 'in case there are no constants (eg. if you repeat the insert straightaway)
.Offset(-1).SpecialCells(xlCellTypeConstants, 23).ClearContents
On Error GoTo 0 'restore error reporting
Application.CutCopyMode = False 'only needed if there were no constants to clear.
End With
End Sub

[Kieranz]

To p45cal
Dear Sir
What can I say except:
Wow, wow, what mastery!
Just 8 lines!

Many many thanks.
Kie
PS/ not sure how to mark as solved.