Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Очистка содержимого по фильтру
 
#1
21.01.2019 16:10:03
Здравствуйте!

Есть макрос, с помощью которого очищаются данные по условию.
Например, есть таблица с  шапкой из 7 столбцов.
С помощью макроса в столбце 5 фильтруются и очищаются строки со значением 1 и 2.
Исходные данные на листе 1. То что получается, представлено на листе 2.

Потом переходит на другой лист и т.д.

Все работает, но загвоздка в том, что файлы, где планируется это применять, очень громоздкие (около 100 мб), а сами таблицы, которые нужно фильтровать содержат сотни тысяч строк. И при применении макроса все подвисает на некоторое время. Можно ли как-то оптимизировать макрос для его быстродействия.

Я уже изменил функцию удаления строк на очистку содержимого (до этого файл вообще зависал и не реагировал), и убрал обновление экрана. Может есть еще способы или идеи?

Собственно файл и сам код:
Код
Sub www()
Application.ScreenUpdating = False
   With [b8].CurrentRegion
       .AutoFilter Field:=5, Criteria1:="1"
       .Offset(1).SpecialCells(12).EntireRow.ClearContents
       .AutoFilter Field:=5, Criteria1:="2"
       .Offset(1).SpecialCells(12).EntireRow.ClearContents
        End With
   ActiveSheet.AutoFilterMode = 0
   Application.ScreenUpdating = True
End Sub
 
 
 
#2
21.01.2019 17:45:40
mos_art, как минимум можно фильтровать по двум условиям одновременно
Код
Sub www()
Application.ScreenUpdating = False
   With [b8].CurrentRegion
       .AutoFilter Field:=5, Criteria1:="1", Operator:=xlOr, Criteria2:="2"
       .Offset(1).SpecialCells(12).EntireRow.ClearContents
        End With
   ActiveSheet.AutoFilterMode = 0
   Application.ScreenUpdating = True
End Sub
 
 
 
#3
21.01.2019 17:55:00
Казанский, а если более двух условий? Operator:=xlOr, как прочитал, применяется только для фильтрации по 2м условиям.
 
 
 
#4
21.01.2019 18:03:33
Код
Sub www()
Application.ScreenUpdating = False
   With [b8].CurrentRegion
       .AutoFilter Field:=2, Criteria1:=Array("100" _
        , "25", "28", "33", "34", "38", "42", "46", "47", "48", "52", "55", "57", "60", "61", "65", _
        "67", "70", "74", "81", "95"), Operator:=xlFilterValues
        End With
   Application.ScreenUpdating = True
End Sub
Изменено: Михаил Витальевич С. - 21.01.2019 18:07:50
 
 
 
#5
21.01.2019 18:04:41
Привет!
Цитата
mos_art написал:
а если более двух условий?
"Огласите весь список, пожалуйста"
Сравнение прайсов, таблиц - без настроек
 
 
 
#6
21.01.2019 18:25:06
Цитата
mos_art написал:
файлы, где планируется это применять, очень громоздкие (около 100 мб), а сами таблицы, которые нужно фильтровать содержат сотни тысяч строк
1. Excel - не совсем подходящий инструмент для работы с такими объемами данных. Подумайте о переезде на Access или другую СУБД.
2. Очищать строки вместо удаления - неправильный подход, к "дырявой" таблице трудно применять сортировку, фильтр и т.д.
3. Опыт показывает, что перенос нужных данных в другое место работает быстрее, чем удаление ненужных данных на месте. Это можно сделать расширенным фильтром, у него можно указать более двух условий. Попробуйте
Код
Sub Макрос2()
Dim sh As Worksheet, a$
  Set sh = ActiveSheet
  Worksheets.Add , sh
  sh.Range("A1:G1").Copy Range("A1") 'заголовой новой таблицы
  Range("E1").Copy Range("I1:J1") 'заголовок диапазона критериев
  Range("I2") = "<>1"
  Range("J2") = "<>2"
  'и т.д.
  
  sh.Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("I1:J2"), CopyToRange:=Range("A1:G1"), Unique:=False
  Range("I1:J2").ClearContents
  a = sh.Name
  Application.DisplayAlerts = False
  sh.Delete
  Application.DisplayAlerts = True
  ActiveSheet.Name = a
End Sub
Изменено: Казанский - 21.01.2019 18:26:35
 
 
 
#7
21.01.2019 18:29:05
Inexsu, в одном файле на отдельных листах есть разные таблицы.
В каждой таблице разное кол-во столбцов, поэтому номер столбца будет меняться в зависимости от таблицы.
Условия, по которым нужно отфильтровать и удалить данные в таблицы, везде одинаковы.

То есть, на листе 1, нужно в 5 столбце удалить (очистить) все строки где есть определенные значения (например, 1, 2 и 3). Количество этих значений будет разнится, в зависимости от того, файл с какими данными мне надо будет подготовить, но значений как, правило не более 10.
Номера столбцов всегда будет одни и те же для каждой страницы.

Потом аналогичную операцию надо сделать на другом листе, но только там номер столбца будет другой.

В последствии я поищу, как сделать форму, чтобы при запуске макроса мне предлагалось выбрать значения которые надо удалить, чтобы не менять тело макроса вручную каждый раз
 
 
 
#8
21.01.2019 18:37:36
Казанский, спасибо за код. По вашим доводам:
1. Да согласен, Excel не очень подходит, но на горизонте 1-2 лет, точно придется и дальше работать в нем, хотя необходимость другого формата хранения данных осознаем, более того, данные можно загрузить в нашу базу и выкачать потом нужные, но, во-первых, по времени, пока, это гораздо дольше чем удалить вручную. А во-вторых менее оперативно, так как при добавлении или изменении данных нет возможности оперативно это сделать автоматически и быстрее "ручками".  К тому же в этом екселе есть сводные которые, завязаны на этих данных, и эти ексели мы рассылаем.
2. К "дырявой" таблице достаточно просто применить фильтры, достаточно просто выделять таблицу, через столбцы
3. Как уже упоминал, на этих данных завязаны сводные таблицы, и после такого переноса, надо будет им обновлять источник данных
 
 
 
#9
21.01.2019 18:42:14
Re:Оптимизация быстродействия макроса по удалению строк
Фильтр сошел на полпути.
Изменено: RAN - 21.01.2019 18:42:58
 
 
 
Страницы: 1
Наверх