No Excel 2016, como insiro uma linha de tabela sem fragmentar a formatação condicional?

Question

Para duplicar uma linha da tabela, estou usando esse excelente código de macro (aprendido aqui) vinculado a uma tecla de atalho:

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

Mas isso fragmenta a formatação condicional (CF), pois ela insere a formatação, além de dividir as informações do CF "aplica-se a". Antes da inserção, o CF se parece com:

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

Depois que uma nova linha é inserida na linha 10 pela macro acima, ela se torna:

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

Como posso duplicar as fórmulas e valores sem fragmentar essa formatação condicional?

    

Answer 1

Bem, acabei descobrindo. O código de macro a seguir (que uso o tempo todo com Shift-Ctrl-Insert) adiciona linhas, mesmo em tabelas, sem fragmentar a formatação condicional.

    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

Eu tenho visto inúmeras reclamações sobre a fragmentação do formato CF e não consegui encontrar uma solução que funcione. Isto parece ser, e eu certamente saúdo sugestões e melhorias.

(Mais tarde) Um benefício colateral que acabei de observar: as iterações anteriores de duplicação de linha teriam problemas na primeira linha de um intervalo nomeado. Duplicou a linha, mas a nova primeira linha estava fora do intervalo. Em outras implementações, foi a última linha que precisava ser evitada. Este código parece funcionar bem na primeira e na última linha.

(mais tarde ainda) Editado para adicionar manipulação de versões do Excel anteriores ao Excel 2016. Agora parece funcionar novamente para o Excel 2007.