Страницы: 1
RSS
VBA. Удаление строк, в которых непустая ячейка в столбце
 
Уважаемы Знатоки! Помогите, пожалуйста, ускорить макрос.
Задача: есть файл (пример прикреплен), в котором более 30 тысяч строк. По 2м полям нужно сделать проверку и удалить строки не проходящие ее.
В данном случае, нужно оставить строки, у которых колонка М пуста.
Мой код делает это оооочень долго на таком большом количестве строк. Помогите его ускорить. Возможно делать как это через фильтр.. но я не могу понять, как ему сказать какой диапазон потом удалять, ведь первая строка после шапки таблицы может быть не 2й, а например 44й

Я использую такой код:
Код
    Dim LastRow As Long, i As Long
    LastRow = Cells(Rows.Count, "M").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Cells(i, "M").Value <> "" Then Rows(i).Delete
    Next
 
Мария -,  здравствуйте. Вы кусочек файла приложите (Правила:  п. 2.3. Приложите файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.)
 
Сортируете диапазон по М, удаляете всё скопом. Затем можно отсортировать назад, если важен изначальный порядок.
Именно так работает быстрый код удаления строк от ZVI, где-то тут на форуме он есть.
Ну а в этом нужно отключить обновление экрана и пересчёт - на 30000 должно хватить ускорения
 
Мария -, ну навскидку удаление сделать массово а не каждый раз дял этого сделать через union()
Не бойтесь совершенства. Вам его не достичь.
 
Вот ТУТ есть варианты, может, что-то подойдет. Первый вариант попробуйте. Выделите значения в столбце М, а дальше по инструкции, только в самом конце не удалить ячейки, а удалить строки
Изменено: msi2102 - 23.09.2020 15:46:27
 
ZVI DelRows:
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=8&TID=24531&TITLE_SEO=24531&MID=335928#message335928
Изменено: Hugo - 23.09.2020 15:43:54
 
Цитата
Mershik написал:  ...удаление сделать массово а не каждый раз дял этого сделать через union()
Где-то так:
Код
Dim LastRow As Long, i As Long, rows_del as range, rows_tmp as range
    set rows_del=nothing
    LastRow = Cells(Rows.Count, "M").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Cells(i, "M").Value <> "" Then
            set rows_tmp=rows(i)
            if rows_del is nothing then set rows_del=rows_tmp else set rows_del=union(rows_del,rows_tmp)
        end if
    Next
    rows_del.Delete
 
Вот ещё нашлось по теме (но не вникал):
Excel VBA Performance - 1 million rows - Delete rows containing a value, in less than 1 min
https://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less
 
Александр Макаров, спасибо ОГРОМНОЕ! Код работает и горааааздо быстрее, чем мой!
 
все Hugo, msi2102,  расходимся ваши предложения не помогли)
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
Mershik написал:
все  Hugo ,  msi2102 ,  расходимся ваши предложения не помогли)
Да, нужно было решение, а не путь к нему.
 
На stackoverflow почему-то метод с union признан самым медленным... Но это может потому что было 5 years, 3 months ago :)
 
Есть и такой вариант:
Код
[M:M].SpecialCells(4).EntireRow.Delete
Чем шире угол зрения, тем он тупее.
 
Кстати вроде как у union есть ограничение по количеству несмежных, вот сижу тестю - 6960,6961,6962... медленно так набирает уже, когда до 8000 доберётся не знаю...
 
Hugo,  ждем)
Не бойтесь совершенства. Вам его не достичь.
 
8700
...
8950
ну оочень медленно уже, но пока в ошибку не выпало
9020
нафиг. прекращаю, надоело :)
Изменено: Hugo - 23.09.2020 17:25:08
 
Hugo, нужно на ночь поставить)
Не бойтесь совершенства. Вам его не достичь.
 
Т.е. если как заявлено в диапазоне 30 тысяч строк, и удалять нужно унионом каждую вторую (ну вдруг так карта легла) - то тот код нужно запускать на ночь :)
 
Hugo, короче поставил вот такой макрос на рабочем компе и пошел домой)
надеюсь лишь на одно, что таймер правильно указал и будет время)
Не бойтесь совершенства. Вам его не достичь.
 
Я сдуру замахнулся сразу на миллион :)
Код
Sub tttt()
    Dim i&, delra As Range
    For i = 1 To 1000000 Step 2
        If delra Is Nothing Then Set delra = Cells(i, 1) Else Set delra = Union(delra, Cells(i, 1))
        Application.StatusBar = delra.Cells.Count
        DoEvents: DoEvents
    Next
End Sub

Может конечно тормозило извлечение delra.Cells.Count... :)
Я собирался дождаться когда вывалится в ошибку.
Изменено: Hugo - 23.09.2020 18:00:06
 
Добрый день! Удаление несмежных строк - долгий процесс, независимо от того, получены ли они методом Union (медленным), автофильтром или методом Range.SpecialCells.
Владимир (ZVI) еще в 2009 году (или раньше?) предложил отсортировать строки исходного диапазона по признаку удаления, а затем применить одно удаление уже смежных строк (см. #6). Даже для миллиона строк такой подход занимает секунды. В ссылке из сообщения Игоря #8 автор на stackoverflow в 2015 году "переоткрывает" этот метод.
Изменено: sokol92 - 23.09.2020 19:28:20
Владимир
 
Цитата
Мария - написал:
Удаление строк, в которых пустая ячейка в столбце
Цитата
Мария - написал:
В данном случае, нужно оставить строки, у которых колонка М пуста.
одга фраза противоречит другой,  так что вам нужно???
удалитиь все строки где М пуста или оставить все строки где  М пуста?
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Да уже и не важно.
Есть кстати ещё способ с ColumnDifferences, но там тоже вроде были ограничения по количеству.
 
Hugo, ну короче вот столько  3410,5  секунд) на 30000 строк
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
Мария - написал:
В данном случае, нужно оставить строки, у которых колонка М пуста.
Просьба модераторам поменять тему с
--VBA. Удаление строк, в которых пустая ячейка в столбце--
на
--VBA. Удаление строк, в которых непустая ячейка в столбце--
 
Я вообще не понимаю зачем здесь VBA, можно сделать гораздо проще, а главное быстрее:
Страницы: 1
Наверх