DELETE from ListObject table rows meeting condition using VBA

Question

Is it possible in VBA to DELETE all the rows meeting condition from Excel ListObject table? I am looking for something similar to SQL statement:

DELETE FROM MyListObjectTable WHERE MyColumn='Some Condition'

Is it connected to a source? Do you want to delete them from the source? If not I'd use the condition <> in the sql for the table. Have you tried anything? — Nathan_Sav
– Nathan_Sav, Commented Mar 28, 2017 at 11:05
It it connected to SQL, however I can break that connection, if necessary. I tried to make it through ADO Connection similar to the idea you can find here: stackoverflow.com/a/33807584/1903793 — Przemyslaw Remin
– Przemyslaw Remin, Commented Mar 28, 2017 at 11:28
use the listobject.querytable.connection to create the ado and execute the sql you have, then refresh the table. — Nathan_Sav
– Nathan_Sav, Commented Mar 28, 2017 at 11:33
if some condition exists in C3 then Set listObj = ActiveSheet.ListObjects("Table1") -next line - listObj.ListRows(3).Delete — John Muggins
– John Muggins, Commented Mar 28, 2017 at 13:50
@JohnMuggins I have a huge table. I would like make bulk deletes, not single or looplike. Better from your proposal would be deleting with auto-filter. However SQL-like statements are much more superior for the sake of clarity. — Przemyslaw Remin
– Przemyslaw Remin, Commented Mar 28, 2017 at 14:03

EEM · Accepted Answer · 2017-03-28 23:29:06Z

1

Try this:

.- Break the connection,

.- Sort the ListObject as per condition (Optional just to have the results of the condition in one Range.Area for the deletion),

.- AutoFilter the ListObject as per condition,

.- Delete visible rows of the ListObject.DataBodyRange

.- Clear AutoFilter

answered Mar 28, 2017 at 23:29

EEM

6,6602 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1