none
ListObject (Table) - VBA to Delete multiple rows

    Question

  • Is it possible to delete more than one row at a time ?

    The ListRow object's Delete method only has a single parameter.
    However, from the GUI, one can select multiple rows within the table and then all rows can be deleted by context menu option.

    Monday, August 13, 2012 5:19 PM

Answers

  • Thanks. Interesting....Excel is table-aware.

    I had forgotten that the address must be a string, so this is probably best:

    [Table1].Rows("3:4").Delete

    In that manner, only data rows within the table are insured to be present and then deleted.

    Monday, August 13, 2012 6:33 PM

All replies

  • You can delete them as Rows.  Adapted from the Recorder:

    Sub ARowsByAnyOtherName()
        Range("A1:D5").Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$5"), , xlYes).Name = _
            "Table1"
        Rows("3:4").Select
        Selection.Delete Shift:=xlUp
    End Sub


    gsnu201208

    Monday, August 13, 2012 6:24 PM
  • Using ListRows directly you can only refer to one row at a time, or all the rows. So no, you can only delete one row at a time, eg delete rows 2, 3 & 4 in reverse order

    Set lo = ActiveSheet.ListObjects("Table1")
    For i = 4 To 2 Step -1
    lo.ListRows(i).Delete
    Next

    However you could use the Range method and delete all in one go, eg

    Set lo = ActiveSheet.ListObjects("Table1")
    lo.ListRows(2).Range.Resize(3).Delete shift:=xlUp

    Peter Thornton

    Monday, August 13, 2012 6:26 PM
  • If you record a macro of deleting multiple rows from a table, you'll find that Excel creates a line for each individual row.

    Moreover, using ListRows(r).Delete is painfully slow.

    Workarounds:

    • Delete the corresponding Range object, or
    • Add a column with a dummy header, and enter the header name in all rows that you want to delete (e.g. enter the word Delete in the header and in all rows to be removed); then use the RemoveDuplicates method to remove the rows.

    Regards, Hans Vogelaar

    Monday, August 13, 2012 6:29 PM
  • Thanks. Interesting....Excel is table-aware.

    I had forgotten that the address must be a string, so this is probably best:

    [Table1].Rows("3:4").Delete

    In that manner, only data rows within the table are insured to be present and then deleted.

    Monday, August 13, 2012 6:33 PM
  • Dude - sorry, but that is the worst code Excel VBA code possibly in the history of mankind.

    I would delete the post if I were you.

    Wednesday, February 27, 2013 1:25 AM
  • That works for me:

      wksSpvOverview.ListObjects("boo").AutoFilter.ShowAllData
      wksSpvOverview.ListObjects("boo").DataBodyRange.Delete


    Juliusz

    Wednesday, March 06, 2013 5:09 PM