CODEWITHSUNDEEP
excelvba
aNobleNoob
aNobleNoob

Reputation: 111

How to delete the active table row in a ListObject?

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...

Upvotes: 2

Views: 5516

Answers (3)

Vityata
Vityata

Reputation: 43585

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

Upvotes: 1

Robert Todar
Robert Todar

Reputation: 2145

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

Upvotes: 2

Mr. Data
Mr. Data

Reputation: 71

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.

Upvotes: 0

Related Questions