Писал большой макрос и возникла необходимость удалять из умной таблицы данные по критерию в одном столбце. Алгоритм нехитрый: фильтруем умную по нужному значению в нужном столбце, удаляем видимые, снимаем фильтр. И всё бы ничего, если бы это можно было сделать обычным (привычным) способом, типа [именованный_диапазон_в_умной_таблице].SpecialCells(xlCellTypeVisible).EntireRow.Delete, но так сделать нельзя. Можно циклом, но это долго. В то же время, выделить нужный столбец, потом выделить видимые и удалить эти строки целиком (всё стандартными встроенными командами) вполне можно, но, если записать всё это макрорекордером и запустить, то не выйдет (странно)… Вот такие пироги
Единственный вариант без Select'ов и Activate'ов, который я нашёл это ровно такая же строка, но без .EntireRow. Однако, дорожка это кривая: в определённых ситуациях (диапазон из одной области по столбцу(-цам)) таким способом можно удалить не только строки целиком, но и столбцы.
Поделитесь опытом - как быть?
Макрос из файла
Код
Option Explicit
' Пробуем удалить отфильтрованные ячейки (строки целиком). Фильтр по первому столбцу поставлен макросом: 1 и 3
Private Sub Main()
Dim t
Application.ScreenUpdating = 0: On Error Resume Next
[_DBFilter].EntireRow.Delete: [_reserveBody].Copy: shDB.[a2].PasteSpecial Paste:=xlPasteValues
On Error GoTo er
t = Timer
shDB.ListObjects(1).Range.AutoFilter 1, "=1", xlOr, "=3"
Application.DisplayAlerts = 0
[_DBFilter].SpecialCells(xlCellTypeVisible).Delete ' вот эта штука опасная
Application.DisplayAlerts = 1
shDB.ListObjects(1).Range.AutoFilter
MsgBox "Время работы (сек.): " & Round(Timer - t, 4), vbInformation, "ГОТОВО": GoTo fin
er: MsgBox "Что-то пошло не так…", vbCritical, "НЕПРЕДВИДЕННАЯ ОШИБКА"
fin: On Error GoTo 0: Application.ScreenUpdating = 1
End Sub
'============================================================================================
Sub Рекордер()
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
End Sub
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
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 вообще не хочет.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
вот и я о том же)) мало того, что Select'ы приходится использовать (а я стараюсь вообще без них обходиться), так ещё "разбивать", а потом обратно "собирать" "умную" а так - вариант, конечно, но не хотелось бы… Спасибо
Если никто больше не подскажет, то думаю вот что: 1. до 5-10к строк - циклы (можно снизу вверх по строкам, но лучше собирать всё в Union, а потом скопом удалить) 2. всё, что более, будет чуть сложнее
копируем данные "умной" на временный лист (вставить значениями)
полностью очищаем "умную" (EntireRow.Delete) по любому столбцу. Формулы при этом сохранятся
на временном листе быстро-быстро делаем все манипуляции
копируем данные со временного листа обратно в "умную"
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
- удаляет по одной циклом от последний строк к первым... коллективного метода не нашел: только преобразовать таблицу в диапазон, удалить выбранное, одеть таблицу на то, что осталось
Sub qq()
Dim r As Range
Dim ar As Range
Set r = ActiveSheet.ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow
For Each ar In r.Areas
ar.Delete
Next
End Sub
Вариант, с удалением строк. На тестовом примере около 5 секунд. Чтобы было быстрее, по идее лучше с начала отсортировать по ключам фильтра (предварительно вставив временный индекс столбец для восстановления порядка. Хотя если там хитрозакрученные формулы будут, то можно и замедлить), удалить и восстановить по индексу исходную сортировку.
Скрытый текст
Код
Private Function getVisibleTableCells(ByVal inTable As ListObject) As Range
On Error GoTo errHandle
Set getVisibleTableCells = inTable.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
Exit Function
errHandle:
Set getVisibleTableCells = Nothing
End Function
Public Sub delVisibleTableRows()
Dim pLo As ListObject, t As Single, visibleRange As Range
t = Timer
Set pLo = shDB.ListObjects(1)
pLo.Range.AutoFilter 1, "=1", xlOr, "=3"
Set visibleRange = getVisibleTableCells(pLo)
If Not visibleRange Is Nothing Then
Application.ScreenUpdating = False
pLo.ListColumns(1).DataBodyRange.EntireRow.Delete
Application.DisplayAlerts = True
End If
MsgBox Timer - t
End Sub
Второй вариант, это то, что предложил Игорь, только он требует сброса фильтрации, иначе на 2010. ListRows®.Delete спокойно выполняется, только ничего не удаляется, поэтому применяется сброс фильтра. Естественно, будет самым медленным, у меня выполняется 7 секунд. Правда, есть выгода, не удаляются данные вне таблицы, совпадающие с номерами видимых строк.
Скрытый текст
Код
Public Sub delVisibleTableRows2()
Dim pLo As ListObject, t As Single, visibleRange As Range
Dim pCell As Range, i As Long, vOffset As Long, rowIds() As Long
t = Timer
Set pLo = shDB.ListObjects(1)
pLo.Range.AutoFilter 1, "=1", xlOr, "=3"
Set visibleRange = getVisibleTableCells(pLo)
If Not visibleRange Is Nothing Then
vOffset = pLo.HeaderRowRange.Row
ReDim rowIds(1 To visibleRange.Count)
i = 0
For Each pCell In visibleRange
i = i + 1
rowIds(i) = pCell.Row - vOffset
Next
Application.ScreenUpdating = False
pLo.AutoFilter.ShowAllData
For i = UBound(rowIds) To 1 Step -1
pLo.ListRows(rowIds(i)).Delete
Next
Application.DisplayAlerts = True
End If
MsgBox Timer - t
End Sub
Здравствуйте, коллеги! Поддерживаю Андрея в такой редакции (модификация известного метода ZVI): добавить в таблицу столбец с формулой (0-не удалять, 1-удалять), отсортировать по этому столбцу, удалить строки. Временный индекс для восстановления порядка, мне кажется, не нужен, как как сортировка Excel всегда после ключей сохраняет первоначальный порядок строк.
Господа, спасибо вам огромное! Знатно вы мне мне тут идей накидали))) Завтра-послезавтра надеюсь всё подробно затестить. По итогам обязательно отпишусь и тогда уже жду ваших комментариев))) Подписывайтесь на канал, ставьте лайки (шутка ) не отписывайтесь от темы, пожалуйста - надо полевые испытания провести с замером скорости и удобства
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: надо полевые испытания провести
Редакция, описанная выше:
Скрытый текст
Код
Public Sub delVisibleTableRows3()
Dim pLo As ListObject, pLc As ListColumn, t As Double, i As Long, n As Long
t = Timer
Set pLo = shDB.ListObjects(1)
Set pLc = pLo.ListColumns.Add ' добавочный столбец
pLc.Range.Cells(2, 1).FormulaR1C1 = "=IF(OR(RC[-4]=1,RC[-4]=2),1,0)"
With pLo.Sort ' сортировка
.SortFields.Clear
.SortFields.Add pLc.Range
.Apply
End With
i = Application.WorksheetFunction.CountIf(pLc.DataBodyRange, 1)
If i > 0 Then ' удаление строк
With pLc.Range
n = .Cells.Count
.Offset(n - i).Resize(i).EntireRow.Delete
End With
End If
pLc.Delete ' удаление добавочного столбца
MsgBox Timer - t
End Sub
sokol92, благодарю вас! Всё ещё занимаюсь большим проектом (пока ограничился циклом в силу небольшого объёма). Как только сдам (надеюсь завтра) - устрою тест-драйв. Радует что принцип работы и логика всех предложенных вариантов ясны
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Я закончил тесты. На 5к строк все методы, связанные с работой в отфильтрованном диапазоне в 10-20 раз медленнее метода с созданием дополнительного столбца с формулой фильтра и последующей сортировкой (от 4 секунд с вариантами от Андрея и до 10-20 секунд с вариантами от Игоря и RAN против 0,28-0,33 сек варианта с сортировкой). И это при том, что столбец надо создать и удалить. а это небыстро для умных таблиц. Метод предложил Андрей и sokol92, и, как я понимаю это демонстрировал легендарный ZVI. Что ж…прекрасный приём обхода фильтрации, надо сказать - взял на вооружение! И, кстати, не сразу допёр, но ведь действительно
Цитата
sokol92 написал: Временный индекс для восстановления порядка, мне кажется, не нужен, как как сортировка Excel всегда после ключей сохраняет первоначальный порядок строк.
потому что, когда мы сортируем по временному столбцу, это просто позволяет быстро удалять строки, а порядок оставшихся никак не меняется (при условии, что все строки, попадающие под критерии удаляются, разумеется). Это круто! Немного изменил код, убрав парочку необязательных расчётов. Пробовал заменить EntireRow на Rows (по методу удаления сплошного диапазона от Казанского, и никакой разницы в скорости не заметил. EntireRow лично мне удобнее в написании)))
В завершении хочу сказать огромное спасибо всем откликнувшимся! Разумеется, это не соревнование и все предложенные здесь методы являются новыми для меня и знать их просто необходимо (тем более с моей любовью к "умным" таблицам). Спасибо вам большое за науку!
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Выкладываю функцию, позволяющую удалить строки независимо, простой ли это диапазон или умная таблица. Без циклов
Код
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 сработает.
P.S.: если у вас области умной таблицы разделены на область ввода и область формул, то есть столбцы для ввода и формульные не перемешаны и можно выделить одной областью весь диапазон для ручного ввода (см. столбцы "B-L" на скрине), то есть способ, который на больших объёмах даст существенный выигрыш: забираем область ввода в массив, удаляем все строки умной таблицы (см. выше), фильтруем массив в памяти, выгружаем обратно в таблицу (в моём примере со скрина [b2].Resize(ubound(arr,1),ubound(arr,2)).value2=arr)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄