2

I have a userform for inputting new rows of data into a dynamic table that is synced to a Sharepoint list. I just need the VBA code for deleting the active row in the table only.

I know this code is for deleting a specific row in the table:

Selection.ListObject.ListRows(8).Delete

But I need to delete the active row and only the active row in the table...

Robert Todar
  • 2,085
  • 2
  • 11
  • 31
aNobleNoob
  • 111
  • 1
  • 9
  • 1
    How do you know which is the active row in the table? `ActiveCell.Row` can tell you which row of the worksheet is active. – PeterT Jun 18 '19 at 15:10
  • I'm aware of how to get the active row, I need the code for deleting the active listrow of the table. Not through the selection of the entire row or a rang of cells, I need to delete the actual list row of the table – aNobleNoob Jun 18 '19 at 15:27

3 Answers3

1

You can get the active table row using:

Selection.Row - Selection.ListObject.Range.Row

Putting it together with what you have:

Dim ActiveTableRow As Long
ActiveTableRow = Selection.Row - Selection.ListObject.Range.Row
Selection.ListObject.ListRows(ActiveTableRow).Delete
Robert Todar
  • 2,085
  • 2
  • 11
  • 31
1

Avoiding the usage of Select and Activate is probably the first step to become a better VBA developer - How to avoid using Select in Excel VBA

Thus, the easiest way to do it is this one:

Sub TestMe()

    Cells(ActiveCell.Row, 1).EntireRow.Delete

End Sub

However, if you like to go one step further, then a more general function can be created:

Sub DeleteRow(rowNumber As Long, _
                Optional ws As Worksheet, _
                Optional wb As Workbook)

    If wb Is Nothing Then
        Set wb = ThisWorkbook
    End If

    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If

    ws.Cells(rowNumber, 1).EntireRow.Delete

End Sub

Sub TestMe()

    DeleteRow ActiveCell.Row

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
0

Here is the code for deleting the active row.

x = ActiveCell.Row
Rows(x).Select
Selection.ActiveRow.Delete

This help? Works in a table or grid.

Mr. Data
  • 71
  • 6
  • I'm aware on how to delete an active row. What I am looking to do though is remove a list row from a table. Deleting an active row just clears contents in all the cells of the active row. I need to remove a row from a table and that row needs to be the active row. – aNobleNoob Jun 18 '19 at 15:28