Excel снять фильтр на всех листах - Word и Excel - помощь в работе с программами

D07

Пользователь

Сообщений: 60
Регистрация: 06.06.2014

Добрый день.
Вопрос копеечный для тех, кто понимает. В архиве даже была такая тема, но там нет ответа на мой вопрос.
Через рекордер макросов записал простейший макрос на снятие фильтров через кнопку Данные-Очистить.
Завел макрос на кнопку. Макрос работает, если включены фильтры. Нет вопросов. А вот если нажать кнопку, когда фильтров нет — он выдает ошибку вот в этой (и единственной ) строке макроса

— ActiveSheet.ShowAllData

Я так понимаю, что нужно добавить что то, чтобы при нажатии на кнопку при отсутствии фильтров, он просто вхолостую щелкал и все. Но я в этом ни бум-бум. Помогите пожалуйста.

SAS888

Пользователь

Сообщений: 757
Регистрация: 01.01.1970

01.03.2018 06:36:09

Если требуется «Отобразить все» не зависимо от того, применен автофильтр или нет, то можно так:

Код
On Error Resume Next: ActiveSheet.ShowAllData: On Error GoTo 0

Если же требуется вообще удалить автофильтр с листа, то так:

Код
ActiveSheet.AutoFilterMode = False

Чем шире угол зрения, тем он тупее.

D07 Пользователь Сообщений: 60 Регистрация: 06.06.2014	«Если требуется «Отобразить все» не зависимо от того, применен автофильтр или нет, то можно так:» Да, Вы меня правильно поняли. Вставил в макрос — не работает . Выдает ту же ошибку в той же строке

D07 Пользователь Сообщений: 60 Регистрация: 06.06.2014	Прошу прощения — это я балбес. Нужно было разнести по разным строкам Спасибо, заработало!

DopplerEffect

Пользователь

Сообщений: 527
Регистрация: 14.08.2017

01.03.2018 07:45:56

Код
ActiveSheet.AutoFilterMode = False

Этот код должен работать, сам проверял в работе, а выложите пример файла.

ZVI

Пользователь

Сообщений: 4328
Регистрация: 23.12.2012

01.03.2018 07:53:18

Цитата
SAS888 написал: On Error Resume Next: ActiveSheet.ShowAllData: On Error GoTo 0

Или так: If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Но если строки скрыты вручную или фильтром т.н. «умной» таблицы, то нужно уже так: Cells.Rows.Hidden = False

D07

Пользователь

Сообщений: 60
Регистрация: 06.06.2014

01.03.2018 11:26:33

Цитата
DopplerEffect написал: Этот код должен работать, сам проверял в работе, а выложите пример файла.

Все работает, я просто невнимательно сделал первый раз.

adamm

Пользователь

Сообщений: 830
Регистрация: 29.12.2015

04.11.2019 09:51:03

Всем привет!
Столкнулся почти с аналогичной задачей, так, что не стал создавать отдельную тему, суть следующая есть несколько умных таблиц размещённых на одном листе, нужно снять все фильтры со всех разом, данный код работает:

Код
Cells.Rows.Hidden = False

, но после снятия всех фильтров в заголовках остаются кнопки, вроде как фильтр не снят, но на само деле снят, как можно исправить

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

Пример.xlsm (14.69 КБ)

skais675

Пользователь

Сообщений: 2177
Регистрация: 03.06.2014

04.11.2019 10:07:26

Код
Sub Button1_Click() With ActiveSheet For Each lo In .ListObjects lo.AutoFilter.ShowAllData Next End With End Sub

Мой канал

adamm

Пользователь

Сообщений: 830
Регистрация: 29.12.2015

#10

04.11.2019 10:29:01

skais675, спасибо, я нашёл вот такой код,

http://qaru.site/questions/228230/removing-filters-for-each-table-in-a-workbook-vba/1202718#1202718

Код

Sub ResetFilters()
    Dim ws                    As Worksheet
    Dim wb                    As Workbook
    Dim listObj               As ListObject

    For Each ws In ActiveWorkbook.Worksheets
        For Each listObj In ws.ListObjects
            If listObj.ShowHeaders Then
                listObj.AutoFilter.ShowAllData
                listObj.Sort.SortFields.Clear
            End If
        Next listObj
    Next ws
End Sub

Ваш код, то же работает, но в чём разница?

Jack Famous

Пользователь

Сообщений: 10846
Регистрация: 07.11.2014

OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#11

04.11.2019 10:43:16

Хорошая справка по работе с фильтрами

Цитата
adamm: несколько умных таблиц размещённых на одном листе

очень плохая идея

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

skais675

Пользователь

Сообщений: 2177
Регистрация: 03.06.2014

#12

04.11.2019 10:55:17

В дополнении темы — полное удаление фильтров

Код
Sub RemoveFilters() For Each lo In ActiveSheet.ListObjects If lo.ShowAutoFilter Then lo.ShowAutoFilter = False Next lo End Sub

Мой канал

adamm

Пользователь

Сообщений: 830
Регистрация: 29.12.2015

#13

04.11.2019 11:03:05

Цитата
Jack Famous написал: очень плохая идея

Ситуация так требует, данные таблицы являются промежуточным звеном, выгружаются они из PQ, а к ним привязаны формулы
Хотя вопрос не в этом, решение есть спасибо skais675,

RAN

Пользователь

Сообщений: 7091
Регистрация: 21.12.2012

#14

04.11.2019 13:24:26

Вам нужно удалить фильтры, или сбросить?
Если сбросить, то

Код
Sub RemoveFilters() For Each lo In ActiveSheet.ListObjects lo.ShowAutoFilter = False lo.ShowAutoFilter = True Next lo End Sub

geniux Пользователь Сообщений: 5 Регистрация: 17.10.2019	я пользуюсь таким кодом, если нужно сбросить фильтры на одном листе или несколько (нужно добавить кнопку) Private Sub CommandButton1_Click() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets On Error Resume Next If ws.AutoFilterMode Then ws.AutoFilterMode = False End If Next ws End Sub

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	#16 05.11.2019 00:03:51 geniux, Вы видели, как код выглядит у других?

Источник

Предположим, вы создали несколько фильтров на разных листах в книге Excel и теперь хотите очистить все эти фильтры сразу. Обычно вам нужно переходить к листу один за другим, чтобы проверить фильтр, а затем очистить его вручную. Есть ли удобный способ очистить фильтры со всех листов в активной книге? Пожалуйста, попробуйте метод, описанный в этой статье.

Очистить фильтры со всех листов в активной книге с кодом VBA

Очистить фильтры со всех листов в активной книге с кодом VBA

Запустите приведенный ниже сценарий VBA, чтобы очистить фильтры со всех листов в активной книге.

1. В книге, из которой нужно очистить фильтры, нажмите кнопку другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модули. Затем скопируйте и вставьте приведенный ниже сценарий VBA в окно модуля. См. Снимок экрана ниже:

Код VBA: очистить фильтры со всех листов в активной книге

Sub Clear_fiter()()
'Updated by Extendoffice 20210625
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True

End Sub

3. нажмите F5 ключ для запуска кода. Затем все фильтры на всех листах в текущей книге немедленно очищаются.

Статьи по теме:

Как очистить кеш фильтра (старые элементы) из сводной таблицы в Excel?
Как очистить фильтры при открытии, сохранении или закрытии книги в Excel?
Как фильтровать данные по флажку в Excel?
Как заполнить ряд чисел в столбце отфильтрованного списка в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (11)

Оценок пока нет. Оцените первым!

Источник

Снять фильтр столбца на всех листах книги

китин

Дата: Пятница, 13.01.2017, 12:09 |
Сообщение № 1

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973

Репутация:
1063
±

Замечаний:
0% ±

Excel 2007;2010;2016

Доброго всем времени суток. Собственно вопрос в названии. Возникла необходимость разом снять фильтр со всех( а их 21 лист) листов книги. Попробовал накропать что то самостоятельно, но не хочет работать по всем листам: только на активном.Вопрос: что не так сделал?
[vba]

Код

Sub Макрос1()
  Dim SO As Long, SO1 As Long, sh As Worksheet
  SO = Cells(15, Columns.Count).End(xlToLeft).Column
  SO1 = Cells(Rows.Count, 2).End(xlUp).Row
   For Each sh In ThisWorkbook.Sheets
     If sh.Name <> «План» And sh.Name <> «литье» Then

With sh
ActiveSheet.Range(Cells(17, 1), Cells(SO1, SO)).AutoFilter Field:=2

                                End With
    End If
    Next sh

End Sub

[/vba]

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

buchlotnik

Дата: Пятница, 13.01.2017, 12:18 |
Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

[vba]

Код

‘можно так

Sub Макрос1()
Dim SO As Long, SO1 As Long, sh As Worksheet
SO = Cells(15, Columns.Count).End(xlToLeft).Column
SO1 = Cells(Rows.Count, 2).End(xlUp).Row
For Each sh In ThisWorkbook.Sheets
If sh.Name <> «План» And sh.Name <> «литье» Then

                    With sh
            sh.Activate
            ActiveSheet.Range(Cells(17, 1), Cells(SO1, SO)).AutoFilter Field:=2

                  End With
    End If
    Next sh

End Sub

[/vba]

Сообщение отредактировал buchlotnik — Пятница, 13.01.2017, 12:25

Ответить

SLAVICK

Дата: Пятница, 13.01.2017, 12:22 |
Сообщение № 3

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация:
766
±

Замечаний:
0% ±

2019

разом снять фильтр со всех

если именно снять со всех тогда вот:
[vba]

Код

Sub d()
On Error Resume Next
For Each sh In ActiveWorkbook.Sheets
sh.ShowAllData
Next
End Sub

[/vba]
не увидел что по 2-му столбцу….
Тогда так:
[vba]

Код

Sub dd()
Dim sh As Worksheet
On Error Resume Next
For Each sh In ActiveWorkbook.Sheets
Set fR = sh.AutoFilter.Range
fR.AutoFilter Field:=2
Next
End Sub

[/vba]

К сообщению приложен файл:

9148151.xlsm
(24.1 Kb)

Иногда все проще чем кажется с первого взгляда.

Ответить

китин

Дата: Пятница, 13.01.2017, 13:35 |
Сообщение № 4

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973

Репутация:
1063
±

Замечаний:
0% ±

Excel 2007;2010;2016

Спасибо всем!!!!!!! все получилось!!!

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Alex_ST

Дата: Пятница, 13.01.2017, 14:26 |
Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3176

Репутация:
604
±

Замечаний:
0% ±

2003

[vba]

Код

Sub Reset_All_Sheets_Filters()   ‘ сброс всех фильтров на всех листах активной книги
   Dim actShName$: actShName = ActiveSheet.Name
   Dim actCelAdr$: actCelAdr = ActiveCell.Address
   Application.ScreenUpdating = False
   Dim wsSh As Worksheet
   On Error Resume Next
   For Each wsSh In Sheets
      If wsSh.AutoFilterMode Then wsSh.ShowAllData
   Next
   Application.ScreenUpdating = True
   Sheets(actShName).Activate: Range(actCelAdr).Activate
End Sub

[/vba]

С уважением,
Алексей
MS Excel 2003 — the best!!!

Ответить

Источник

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

Если определенные данные на нем не находятся, возможно, они скрыты фильтром. Например, если на вашем компьютере есть столбец с датами, в этом столбце может быть фильтр, ограничивающий значения определенными месяцами.

Существует несколько вариантов:

Очистка фильтра из определенного столбца
Очистка всех фильтров
Удаление всех фильтров

Очистка фильтра из столбца

Нажмите кнопку Фильтр рядом с заголовком столбца и выберите очистить фильтр <«Имя столбца»>.

Например, на рисунке ниже показан пример очистки фильтра из столбца «Страна».

Примечание: Удалить фильтры из отдельных столбцов нельзя. Фильтры можно отключать для всего диапазона. Если вы не хотите, чтобы кто-то фильтрует определенный столбец, вы можете скрыть его.

Очистка всех фильтров на

На вкладке Данные нажмите кнопку Очистить.

Как узнать, что к данным был применен фильтр?

Если фильтрация применима к таблице на бумаге, в заголовке столбца вы увидите указанные ниже кнопки.

Кнопка	Описание
	Фильтр доступен и не использовался для сортировки данных в столбце.
	Фильтр используется для фильтрации или сортировки данных в столбце.

На следующем сайте фильтр доступен для столбца «Товар», но еще не использовался. Для сортировки данных использовался фильтр в столбце «Страна».

Удалите все фильтры на листе

Если вы хотите полностью удалить фильтры, перейдите на вкладку Данные и нажмите кнопку Фильтр или используйте клавиши ALT+D+F+F.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Фильтрация данных с помощью автофильтра

Фильтрация диапазона данных

Сортировка данных на

Нужна дополнительная помощь?

Источник

На чтение 4 мин. Просмотров 14.2k.

Итог: узнайте, как очистить все фильтры и фильтры в одном столбце с помощью макросов VBA. Включает примеры кода для регулярных диапазонов и таблиц Excel.

Уровень мастерства: Средний

Содержание

Скачать файл
Очистить все фильтры из диапазона
Ошибка метода ShowAllData
Очистить все фильтры из таблицы Excel
Очистить все фильтры во всех таблицах на листе
Очистить фильтры в одной колонке
Фильтры и типы данных

Скачать файл

Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров. Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для более подробной информации.

VBA AutoFilters Guide.xlsm (100.5 KB)

Очистить все фильтры из диапазона

Мы используем метод ShowAllData, чтобы очистить все фильтры,
примененные к диапазону.

Это аналогично нажатию кнопки «Очистить» на вкладке «Данные»
на ленте (сочетание клавиш: Alt, A, C)

К рабочему листу может быть применен только один диапазон
фильтров, поэтому мы на самом деле очищаем фильтры на листе.

Sub Clear_All_Filters_Range()

  ' Для очистки всех фильтров используйте метод ShowAllData
  ' для листа. Добавьте обработку ошибок, чтобы обойти ошибку, если
  ' фильтры не применяются. Не работает для таблиц.
  On Error Resume Next
    Sheet1.ShowAllData
  On Error GoTo 0
  
End Sub

Ошибка метода ShowAllData

Если к любому столбцу не применены фильтры, метод ShowAllData вызовет ошибку. Это ошибка времени выполнения ‘1004 с описанием:
Method ‘ShowAllData’ of object ‘_Worksheet’ failed.

Следующая строка On Error Resume Next будет игнорировать эту
ошибку. При ошибке GoTo 0 сбрасывается, поэтому ошибки возникают в любых
строках кода ниже.

Примечание. Когда метод ShowAllData упоминается как элемент листа, он НЕ очищает фильтры, которые применяются к таблицам Excel (ListObjects), если в таблице не выбрана ячейка. Поэтому лучше всего использовать приведенный ниже код для таблиц.

Чтобы очистить все фильтры таблицы Excel (ListObject), мы
также используем метод ShowAllData. В этом случае ShowAllData является членом
свойства AutoFilter объекта ListObject.

Sub Clear_All_Filters_Table()

Dim lo As ListObject
  
  ' Установить ссылку на первую таблицу на листе
  Set lo = Sheet1.ListObjects(1)
  
  ' Очистить все фильтры для всей таблицы
  lo.AutoFilter.ShowAllData

End Sub

Очистить все фильтры во всех таблицах на листе

Приведенный выше код удаляет фильтры только для одной
таблицы. Мы можем просмотреть таблицы на листе, чтобы удалить все фильтры из
каждой таблицы.

Sub Clear_All_Table_Filters_On_Sheet()

Dim lo As ListObject
  
  ' Перебрать все таблицы на листе
  For Each lo In Sheet1.ListObjects
  
    ' Очистить все фильтры для всей таблицы
    lo.AutoFilter.ShowAllData
    
  Next lo

End Sub

Очистить фильтры в одной колонке

Чтобы очистить фильтры для одного столбца, мы используем
метод AutoFilter. Мы ссылаемся только на параметр Field и устанавливаем
значение для номера столбца, который мы хотим очистить.

Sub Clear_Column_Filter_Range()
  
  ' Чтобы очистить фильтр от одного столбца, укажите
  ' Только номер поля и никаких других параметров
  Sheet1.Range("B3:G1000").AutoFilter Field:=4

End Sub

Поле — это номер столбца диапазона, к которому применяются
фильтры, а не номер столбца рабочего листа.

Тот же метод используется для очистки фильтров, примененных
к столбцу в таблице. В этом случае метод AutoFilter является членом объекта
Range объекта ListObject.

Sub Clear_Column_Filter_Table()

Dim lo As ListObject
  
  ' Установить ссылку на первую таблицу на листе
  Set lo = Sheet1.ListObjects(1)
  
  ' Очистить фильтр в столбце одной таблицы,
  ' указав только параметр поля
  lo.Range.AutoFilter Field:=4
  
End Sub

Фильтры и типы данных

Параметры
раскрывающегося меню фильтра изменяются в зависимости от типа данных в столбце.
У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО
различных комбинаций операторов и критериев для каждого типа фильтра.

Я создал отдельные статьи для каждого из этих типов фильтров. Статьи содержат пояснения и примеры кода VBA.

Как фильтровать числа с помощью VBA
Как отфильтровать пустые и непустые ячейки
Как фильтровать текст с помощью VBA
Как отфильтровать даты по VBA
Как отфильтровать цвета и значки с помощью VBA

Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.

Пожалуйста, оставьте
комментарий ниже с любыми вопросами или предложениями. Спасибо!

Источник

Очистить фильтры со всех листов в активной книге с кодом VBA

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Очистка фильтра из столбца

Очистка всех фильтров на

Как узнать, что к данным был применен фильтр?

Удалите все фильтры на листе

Дополнительные сведения

См. также

Нужна дополнительная помощь?

Скачать файл

Очистить все фильтры из диапазона

Ошибка метода ShowAllData

Очистить все фильтры во всех таблицах на листе

Очистить фильтры в одной колонке

Фильтры и типы данных

А вот еще интересные статьи: