Inserting Row to Table Using VBA

Sara79

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a code that inserts an entire new row and copies the contents from above row. I was hoping to edit the code to insert a row and have it be apart of a table, not just a new row under the table. Any suggestions on how to do this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Me.Unprotect
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
Me.Protect
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,237
In other words you would like adding one row to a Table by double-clicking in its area...
Try this code:
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cTbl As ListObject
Me.Unprotect
Set cTbl = Target.ListObject
If Not cTbl Is Nothing Then
    cTbl.ListRows.Add , True
    Cancel = True
End If
Me.Protect
End Sub
Bye
 

Forum statistics

Threads
1,141,611
Messages
5,707,383
Members
421,508
Latest member
Jalayne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top