Delete List Row

[Papi]

When I use a list in 2003 I use the shortcut Alt > E > W to delete one row. When I record the same keystrokes it converts that to

Selection.ListObject.ListRows(14).Delete

. That unfortunately is row specific. Any idea how to delete a row using a list (or selected rows) using VBA?
Thanks

 

[Al Chara]

Selection.EntireRow.Delete

 

[Papi]

Thanks Al Chara,

Unfortunately the section is inside a 2003 workbook and there is data to the left that cannot be deleted.

 

[Al Chara]

The following code will delete the selected row of a table. If more than one row is selected, only the first row of the selection will be deleted.

Dim MySelection As Range
Dim MyList As ListObject

Set MySelection = Selection
Set MyList = MySelection.ListObject
MySelection.ListObject.ListRows(MySelection.Row - MyList.Range.Row).Delete

Let me know if this doesn't work in Excel 2003 and I'll try to make the necessary modifications.

 

[Papi]

It works great. Thanks Allan.

 

[Papi]

By chance, is there a way to disable the macro at the last line?

 

[Al Chara]

Dim MySelection As RangeDim MyList As ListObject
Dim MyRow As ListRow


Set MySelection = Selection
With MySelection
    Set MyList = .ListObject
    Set MyRow = .ListObject.ListRows(.Row - MyList.Range.Row)
    If MyRow.Index <> .ListObject.ListRows.Count Then MyRow.Delete
End With

 

[Papi]

Hello Allan,

This is great. Sorry to bother you on this last point. The delete works great but if the list expands by one row waiting for the next row then it will not delete the last row (which is exactly what I asked for). Can it go down one more row? If not, no worries, we can do it manually.

 

[Al Chara]

Hello Allan,

This is great. Sorry to bother you on this last point. The delete works great but if the list expands by one row waiting for the next row then it will not delete the last row (which is exactly what I asked for). Can it go down one more row? If not, no worries, we can do it manually.

Not sure I'm following you on this last point. What do you mean by "Can it go down one more row?"

 

[Papi]

The macro does exactly what I asked but I was not thinking about the filter (not sure what they call it) but when the list is on and the cursor is placed at the bottom of the list, the list carries on with formulas etc. If we get to the last row with data it will not delete that row as that is the last row. Does this make sense?