Как удалить строку умную таблицу excel

Доброго времени суток, Планетяне!

Вставка строк рассмотрена

тут

Писал большой макрос и возникла необходимость удалять из умной таблицы данные по критерию в одном столбце.

Алгоритм нехитрый:

фильтруем умную по нужному значению в нужном столбце, удаляем видимые, снимаем фильтр.
И всё бы ничего, если бы это можно было сделать обычным (привычным) способом, типа
[именованный_диапазон_в_умной_таблице].SpecialCells(xlCellTypeVisible).EntireRow.Delete, но так сделать нельзя.
Можно циклом, но это долго. В то же время, выделить нужный столбец, потом выделить видимые и удалить эти строки целиком (всё стандартными встроенными командами) вполне можно, но, если записать всё это макрорекордером и запустить, то не выйдет (странно)… Вот такие пироги  

Единственный вариант без Select‘ов и Activate‘ов, который я нашёл это ровно такая же строка, но без .EntireRow. Однако, дорожка это кривая: в определённых ситуациях (диапазон из одной области по столбцу(-цам)) таким способом можно удалить не только строки целиком, но и столбцы.

Поделитесь опытом — как быть?

Прикрепленные файлы

Изменено: Jack Famous — 06.03.2019 15:26:51

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

может с руками что-то не то?

Private Sub Main()
  Dim t
  Application.ScreenUpdating = 0: t = Timer
  shDB.ListObjects(1).Range.AutoFilter 1, "=1", xlOr, "=3"
  If shDB.ListObjects(1).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then _
    [_DBFilter].SpecialCells(xlCellTypeVisible).EntireRow.Delete
  shDB.ListObjects(1).Range.AutoFilter
  Debug.Print Timer - t: Application.ScreenUpdating = 1
End Sub

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

может, но вы бы тогда и на свои обратили внимание, потому что не работает… Злой вы какой-то стали
К чему проверка на количество видимых ячеек — не понимаю. Как я понял, метод .Delete удалит столбец, если область видимых ячеек одна (отсоритовано типа). А вот .EntireRow вообще не хочет.

Прикрепленные файлы

Изменено: Jack Famous — 29.05.2018 15:35:43

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

как выяснилось, из таблицы строк не выкинешь….

Private Sub Main()
  Dim nm$
  Application.ScreenUpdating = 0
  shDB.ListObjects(1).Range.AutoFilter 1, "=2"
  If shDB.ListObjects(1).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then _
    [_DBFilter].SpecialCells(xlCellTypeVisible).EntireRow.Select
  nm = shDB.ListObjects(1).Name:  shDB.ListObjects(1).Unlist:  Selection.Delete
  shDB.ListObjects.Add(xlSrcRange, [a1].CurrentRegion, , xlYes).Name = nm
  Application.ScreenUpdating = 1
End Sub

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

вот и я о том же)) мало того, что Select’ы приходится использовать (а я стараюсь вообще без них обходиться), так ещё «разбивать», а потом обратно «собирать» «умную»    а так — вариант, конечно, но не хотелось бы… Спасибо  

Если никто больше не подскажет, то думаю вот что:

1. до 5-10к строк — циклы (можно снизу вверх по строкам, но лучше собирать всё в Union, а потом скопом удалить)
2. всё, что более, будет чуть сложнее

• копируем данные «умной» на временный лист (вставить значениями)
• полностью очищаем «умную» (EntireRow.Delete) по любому столбцу. Формулы при этом сохранятся
• на временном листе быстро-быстро делаем все манипуляции
• копируем данные со временного листа обратно в «умную»
• удаляем временный лист

Как-то так  

Изменено: Jack Famous — 29.05.2018 18:04:32

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

.ListRows(r).Delete

— удаляет по одной
циклом от последний строк к первым…
коллективного метода не нашел: только преобразовать таблицу в диапазон, удалить выбранное, одеть таблицу на то, что осталось

Изменено: Ігор Гончаренко — 29.05.2018 18:26:36

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Вариант, с удалением строк.
На тестовом примере около 5 секунд. Чтобы было быстрее, по идее лучше с начала отсортировать по ключам фильтра (предварительно вставив временный индекс столбец для восстановления порядка. Хотя если там хитрозакрученные формулы будут, то можно и замедлить), удалить и восстановить по индексу исходную сортировку.

Второй вариант, это то, что предложил Игорь, только он требует сброса фильтрации, иначе на 2010. ListRows®.Delete спокойно выполняется, только ничего не удаляется, поэтому применяется сброс фильтра. Естественно, будет самым медленным, у меня выполняется 7 секунд. Правда, есть выгода, не удаляются данные вне таблицы, совпадающие с номерами видимых строк.

Изменено: Jack Famous — 29.05.2018 21:37:49

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

Редакция, описанная выше:

Владимир

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

Всем доброго времени суток!

Я закончил тесты. На 5к строк все методы, связанные с работой в отфильтрованном диапазоне в 10-20 раз медленнее метода с созданием дополнительного столбца с формулой фильтра и последующей сортировкой (от 4 секунд с вариантами от Андрея и до 10-20 секунд с вариантами от Игоря и RAN против 0,28-0,33 сек варианта с сортировкой). И это при том, что столбец надо создать и удалить. а это небыстро для умных таблиц. Метод предложил Андрей и sokol92, и, как я понимаю это демонстрировал легендарный ZVI. Что ж…прекрасный приём обхода фильтрации, надо сказать — взял на вооружение! И, кстати, не сразу допёр, но ведь действительно

потому что, когда мы сортируем по временному столбцу, это просто позволяет быстро удалять строки, а порядок оставшихся никак не меняется (при условии, что все строки, попадающие под критерии удаляются, разумеется). Это круто!  
Немного изменил код, убрав парочку необязательных расчётов. Пробовал заменить EntireRow на Rows (по методу удаления сплошного диапазона от Казанского, и никакой разницы в скорости не заметил. EntireRow лично мне удобнее в написании)))

В завершении хочу сказать огромное спасибо всем откликнувшимся! Разумеется, это не соревнование и все предложенные здесь методы являются новыми для меня и знать их просто необходимо (тем более с моей любовью к «умным» таблицам). Спасибо вам большое за науку!  

В процессе штудирования матчасти активно юзал эту

статью о работе с «умными» таблицами

(англ.)

Прикрепленные файлы

Изменено: Jack Famous — 01.06.2018 13:34:05

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

Доброго времени суток, Планетяне!

Выкладываю функцию, позволяющую удалить строки независимо, простой ли это диапазон или умная таблица. Без циклов  

Public Function PRDX_RowsDelete(rng As Range) As Boolean
Dim tbl As ListObject
On Error Resume Next: Set tbl = ActiveWorkbook.Worksheets(rng.Parent.Name).ListObjects(1): Err.Clear: On Error GoTo 0
If Not tbl Is Nothing Then Intersect(rng.EntireRow, tbl.DataBodyRange).Delete Else rng.EntireRow.Delete
PRDX_RowsDelete = True
End Function

Выдаст ошибку, если в умной таблице удалены все строки (одна видна, но тип «дежурная»). Чтобы удалить все строки умной таблицы, нужно выделить диапазон любого столбца (без шапки/заголовка) и нажать кнопку «удалить строки с листа» Если удалять ВСЕ строки «умной» таблицы, то макросное удаление через Range.EntireRow.Delete сработает.

Вдохновляли:
Казанский (

тут

и

тут

)

The VBA Guide To ListObject Excel TablesP.S.:

если у вас области умной таблицы разделены на область ввода и область формул, то есть столбцы для ввода и формульные не перемешаны и можно выделить одной областью весь диапазон для ручного ввода (см. столбцы «B-L» на скрине), то есть способ, который на больших объёмах даст существенный выигрыш:
забираем область ввода в массив, удаляем все строки умной таблицы (см. выше), фильтруем массив в памяти, выгружаем обратно в таблицу (в моём примере со скрина [b2].Resize(ubound(arr,1),ubound(arr,2)).value2=arr)

Прикрепленные файлы

Изменено: Jack Famous — 06.03.2019 15:29:16

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄