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.
ExcelInsertRowImg_220405.jpg

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.