CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Add Row in Table without affecting conditional formatting

I am using this code which works fine, however it messes up conditional formatting in my table. Is there a way to insert a new row in table via VBA without affecting conditional formatting?

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Upvotes: 0

Views: 676

Answers (3)

Error 1004
Error 1004

Reputation: 8230

Try:

Option Explicit

Sub test()

    'Change Sheet name if needed
    With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

        If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
            'Change table name if needed - Insert one row above active cell
            .ListRows.Add ((ActiveCell.Row - ActiveCell.ListObject.Range.Row))
            'Change table name if needed - Insert one row below active cell
            .ListRows.Add ((ActiveCell.Row - ActiveCell.ListObject.Range.Row + 1))
        End If

    End With

End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57733

Add the row only within the ListObject not within the whole sheet (see The VBA Guide To ListObject Excel Tables)

Option Explicit

Public Sub AddRowInListObject()
    Dim ActTable As ListObject
    On Error Resume Next 'next line throws error if ActiveCell is not in a table
        Set ActTable = ActiveCell.ListObject
    On Error GoTo 0 're-activate error reporting!

    If Not ActTable Is Nothing Then 'only add row if ActiveCell is within a table
        ActTable.ListRows.Add ActiveCell.Row - ActTable.Range.Row
    End If
End Sub

Note that there are 2 differnt row counting systems:

  1. ActiveCell.Row which returns the absolute row number of the worksheet
  2. ListRows.Add which awaits the row number relatively to the beginning of the ListObject

So for example if the ListObject starts in row 5 of the worksheet then row number 1 of the ListObject is row number 5 of the worksheet.

Upvotes: 0

Mikku
Mikku

Reputation: 6664

This Will Work:

Public Sub insertRowBelow()

ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteAllMergingConditionalFormats
ActiveCell.Offset(1).EntireRow.Clear
Application.CutCopyMode = False


End Sub

Upvotes: 1

Related Questions