В Excel 2016 как вставить строку таблицы без фрагментации условного форматирования?

Question

Чтобы дублировать строку таблицы, я использую этот отличный код макроса (изученный здесь), связанный с горячей клавишей:

Public Sub InsRow()
    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow    
    ActiveCell.Offset(1, 0).EntireRow.ClearFormats ' suggested by RajeshS below
End Sub

Но это фрагментирует условное форматирование (CF), поскольку оно вставляет форматирование, а также разбивает CF "относится" к информации. Перед вставкой CF выглядит так:

    Applies to: =$A$3:$U$100

После вставки новой строки в строку 10 с помощью вышеупомянутого макроса она становится:

    Applies to: =$A$11:$U:$11
    Applies to: =$A$3:$U$10,$A$12:$U$101

Как я могу дублировать формулы и значения, не разбивая это условное форматирование?

Answer 1

Ну, я понял это. Следующий макрос-код (который я все время использую с Shift-Ctrl-Insert) добавляет строки даже в таблицы без фрагментации условного форматирования.

    Public Sub InsRow()
        Dim cellActive As Range
        Application.ScreenUpdating = False      ' Kill screen flicker
        Set cellActive = ActiveCell             ' Hold the entry cell for later
        ActiveCell.EntireRow.Insert             ' Make the new row
        ActiveCell.Offset(1, 0).EntireRow.Copy  ' Grab the original row now up 1
        ' Handle earlier versions of Excel 
        If Application.Version => 16 Then
            ' xlPasteAllMergingConditionalFormats is defined as 14
            ActiveCell.EntireRow.PasteSpecial Paste:=14 ' 
        Else
            ActiveCell.EntireRow.PasteSpecial 
        End If
        Application.CutCopyMode = False         ' Release the selected row
        cellActive.Offset(-1, 0).Select         ' Pick the original cell position
        Application.ScreenUpdating = True       ' and release the screen
    End Sub

Я видел бесчисленные жалобы на фрагментацию формата CF и не мог найти решение, которое работает. Похоже, что это так, и я, конечно, приветствую предложения и улучшения.

(Позже) Одно дополнительное преимущество, которое я только что отметил: предыдущие итерации дублирования строк имели проблемы с первой строкой именованного диапазона. Это дублировало строку, но новый первый ряд был фактически вне диапазона. В других реализациях это был последний ряд, которого нужно было избегать. Этот код, кажется, отлично работает в первой и последней строках.

(Еще позже) Отредактировано для добавления обработки версий Excel до Excel 2016. Теперь кажется, что работает обратно в Excel 2007.