Ошибка Showwalldata vba excel
Обновлено: 06.07.2024
Примеры макросов автофильтра для использования с автофильтрами рабочих листов (допускается только один на лист).
ПРИМЕЧАНИЕ. Макросы фильтров таблицы Excel можно найти на этой странице: Список макросов автофильтра
Показать все записи
Следующий код Excel AutoFilter VBA показывает все записи, если был применен фильтр.
Показать все записи на защищенном листе
Следующие макросы предназначены для защищенных листов. Есть две версии макроса:
Защищенный лист без пароля
Если лист защищен паролем, используйте этот код, чтобы снять с него защиту, показать все, а затем снова включить защиту.
Защищенный лист паролем
Если рабочий лист защищен паролем, используйте этот код, чтобы снять с него защиту, показать все, а затем снова включить защиту.
Включить или отключить автофильтр Excel
Используйте следующий макрос, чтобы включить автофильтр Excel, если на активном листе нет фильтра. Перейти к началу
Используйте следующий макрос, чтобы отключить автофильтр Excel, если он существует на активном листе
Разгруппировать даты в раскрывающемся списке фильтра
По умолчанию при включении автофильтра даты группируются в раскрывающемся списке. Нажмите на знак плюса, чтобы увидеть месяцы для каждого года.
Если вы не хотите, чтобы даты группировались автоматически, вы можете:
- изменить настройку вручную, чтобы разгруппировать их
- или используйте приведенный ниже макрос, чтобы включить или отключить группировку.
Макрос для включения или отключения группировки по дате
Этот код переключает настройку группировки по дате:
- если группировка по дате включена, макрос отключает ее
- если группировка по датам отключена, макрос включает ее
Скрыть стрелки автофильтра
Возможно, вы хотите, чтобы пользователи фильтровали только определенные поля в списке. Используйте следующие макросы, чтобы скрыть одну или несколько стрелок раскрывающегося списка в строке заголовка списка.
ПРИМЕЧАНИЕ. Эти макросы не отключают автофильтр. Они просто изменяют свойство VisibleDropDown на False для некоторых полей.
Скрыть все стрелки
Следующая процедура Excel AutoFilter VBA скрывает стрелки для всех полей в списке. Функция фильтра НЕ отключена.
Скрыть все стрелки, кроме одной
Следующая процедура Excel AutoFilter VBA скрывает стрелки для всех полей, кроме поля 2.
Вы можете изменить номер поля в переменной iShow, чтобы стрелка другого поля оставалась видимой.
ПРИМЕЧАНИЕ. Используйте номер поля, а НЕ номер столбца листа.
Скрыть стрелки в определенных полях
В некоторых списках вы можете скрыть стрелки в определенных полях и оставить все остальные стрелки видимыми. Следующий макрос скрывает стрелки для полей 1, 3 и 4 -- Случай 1, 3, 4
Вы можете изменить номера полей в первом операторе Case, чтобы скрыть разные стрелки.
ПРИМЕЧАНИЕ. Используйте номер поля, а НЕ номер столбца листа.
Показать стрелки автофильтра
Если одна или несколько стрелок автофильтра скрыты, используйте следующие макросы, чтобы отобразить раскрывающиеся стрелки в строке заголовка списка.
Показать все стрелки автофильтра
Чтобы снова отобразить все стрелки автофильтра, используйте следующий макрос. Когда макрос запускается, он показывает стрелку для каждой ячейки в строке заголовка списка.
Показать все стрелки автофильтра, кроме одной
Следующая процедура Excel AutoFilter VBA показывает стрелки для всех полей, кроме поля 2. Вы можете изменить номер поля в переменной iHide, чтобы скрыть стрелку другого поля.
ПРИМЕЧАНИЕ. Используйте номер поля, а НЕ номер столбца листа.
Копировать отфильтрованные строки
Следующий макрос копирует отфильтрованные строки из активного листа AutoFilter на лист с именем «Лист2».
Использовать автофильтр на защищенном листе
Вы можете использовать автофильтр Excel на защищенном листе, но вы не можете создать автофильтр Excel на защищенном листе. Прежде чем защищать лист, убедитесь, что фильтр на месте.
Чтобы разрешить пользователям использовать автофильтр после того, как лист защищен, обязательно установите флажок "Использовать автофильтр" при защите листа
Включить автофильтр и защитить лист
Чтобы убедиться, что на листе есть автофильтр и лист защищен, используйте макрос, который запускается автоматически при открытии книги, и макросы включены.
Этот макрос Workbook_Open проверяет наличие автофильтра на листе с именем Данные. Включает автофильтр, если его нет на месте. Затем он защищает лист данных и устанавливает защиту только для пользовательского интерфейса. Это позволяет макросам изменять лист, но пользователи не могут вносить изменения вручную.
Сохраните следующий макрос автофильтра Excel на листе модуля ThisWorkbook. Под кодом есть инструкции.
Добавить открытый код рабочей книги
Чтобы добавить этот код в книгу:
- Чтобы открыть модуль ThisWorkbook, нажмите клавиши ALT+F11, чтобы открыть редактор Visual Basic.
- Затем в обозревателе проектов слева найдите свою книгу и нажмите знак +, чтобы увидеть объекты Microsoft Excel.
- Нажмите правой кнопкой мыши на ThisWorkbook и выберите "Просмотреть код".
- Затем вставьте код туда, где мигает курсор.
Подсчет видимых строк
С помощью этого примера кода Excel AutoFilter VBA покажите сообщение с количеством строк, видимых после применения фильтра.
Проверить наличие автофильтра
Чтобы узнать, содержит ли рабочий лист автофильтр, запустите этот макрос. Если на активном листе есть автофильтр рабочего листа, этот код напечатает строку в окне интерпретации с числом единиц. Вы можете использовать этот тип кода в других макросах, чтобы проверить конкретный лист на наличие автофильтра.
Примечание. Пока вы находитесь в редакторе Visual Basic, нажмите Ctrl+G, чтобы отобразить окно Immediate.
Как скопировать код макроса в книгу
В этом видео показано, как скопировать код макроса в обычный модуль кода в рабочей книге. Для письменных шагов перейдите на страницу Копировать код макроса.
Получить книгу макросов
Чтобы просмотреть все макросы с этой страницы, загрузите книгу AutoFilter Worksheet Macros. Заархивированный файл имеет формат xlsm и содержит макросы. Не забудьте включить макросы при открытии книги, если вы хотите протестировать макросы.
У меня есть приведенный выше код, и я хочу, чтобы он очищал автофильтр при каждом сохранении книги. У меня есть событие Workbook_BeforeSave, вызывающее функцию Clear() ниже, но я получаю сообщение об ошибке «Сбой метода ShowAllData класса Worksheet» всякий раз, когда я удаляю
фрагмент кода. Я немного исследовал это и обнаружил, что этот фрагмент кода является ложным. Итак, мой вопрос заключается в том, как мне сделать .filtermode равным true и чтобы эта ошибка не появлялась снова.
На тестовом листе работает следующее:
Проблема, с которой я столкнулся, заключается в том, что .FilterMode имеет значение false, и я не знаю, как сделать его истинным и чтобы .ShowAllData не имел этой ошибки.
Просто добавьте оператор On Error
Спасибо, но это не приведет к тому, что текущие автофильтры будут установлены в false, что мне и нужно каждый раз. Мне нужно удалить фильтры, чтобы отображались все ячейки, но этого не произойдет, если он просто перейдет к следующей строке.
Не режим, а сами фильтры. Скажем, вы фильтруете данные с помощью автофильтра, мне нужно удалить данные с помощью этой функции, но по какой-то причине она не работает, и это потому, что .FilterMode имеет значение false, но мне нужно, чтобы это было правдой.
Кажется, я не понимаю, что вы пытаетесь сделать.
Если вы загрузите книгу с данными и ожидаемым результатом, это может помочь понять вашу проблему.
Значит, вы знаете, как работает автофильтр. Вы выбираете стрелку раскрывающегося списка, и вы можете фильтровать данные. Итак, скажем, в столбце у вас есть такие данные, как 1, 2, 3, 4, 5. Вы используете фильтр, чтобы выбрать все нечетные числа, 1, 3, 5. Мне нужно удалить фильтры, чтобы данные были 1, 2, 3, 4, 5 снова. Это то, что делает функция, но по какой-то причине .filtermode имеет значение false.
Неважно, я только что понял, спасибо за помощь!
Это просто записанный макрос
Вновь созданные сообщения останутся недоступными для других, пока не будут одобрены модератором.
Последний ответ был более 180 дней назад, эта ветка, скорее всего, устарела. Вместо этого рекомендуется создать новую цепочку.
Участвуйте сейчас!
У вас еще нет учетной записи? Зарегистрируйтесь прямо сейчас и станьте частью нашего сообщества!
Итог: узнайте, как очистить все фильтры и фильтры для одного столбца с помощью макросов VBA. Включает примеры кода для обычных диапазонов и таблиц Excel.
Уровень квалификации: средний
Скачать файл
Файл Excel, содержащий код, можно загрузить ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров. Дополнительные сведения см. в моей статье Полное руководство по автофильтрам в VBA.
Руководство по автофильтрам VBA.xlsm (100,5 КБ)
Удалить все фильтры из диапазона
Мы используем метод ShowAllData, чтобы очистить все фильтры, примененные к диапазону.
Это то же самое, что и нажатие кнопки "Очистить" на вкладке "Данные" на ленте (сочетание клавиш: Alt , A , C )
К листу можно применить только один диапазон фильтров, поэтому мы фактически очищаем фильтры на листе.
Ошибка метода ShowAllData
Если ни к одному столбцу не применены фильтры, метод ShowAllData вызовет ошибку. Это ошибка времени выполнения «1004» с описанием: Не удалось выполнить метод «ShowAllData» объекта «_Worksheet».
Строка On Error Resume Next позволяет обойти эту ошибку. При ошибке GoTo 0 сбрасывает это, поэтому ошибки возникают во всех строках кода ниже.
Примечание. Когда на метод ShowAllData ссылаются как на элемент листа, он НЕ очищает фильтры, применяемые к таблицам Excel (ListObjects), если в таблице не выбрана ячейка. Поэтому для таблиц лучше всего использовать приведенный ниже код.
Удалить все фильтры из таблицы Excel
Чтобы очистить все фильтры для таблицы Excel (ListObject), мы также используем метод ShowAllData. В этом случае ShowAllData является членом свойства AutoFilter объекта ListObject.
Удалить все фильтры из всех таблиц на листе
Приведенный выше код очищает фильтры только для одной таблицы. Мы можем просмотреть таблицы на листе, чтобы очистить все фильтры для каждой таблицы.
Очистить фильтры для одного столбца
Чтобы очистить фильтры для одного столбца, мы используем метод автофильтра. Мы ссылаемся только на параметр Field и устанавливаем значение для номера столбца, который мы хотим очистить.
Поле — это номер столбца диапазона, к которому применяются фильтры, а НЕ номер столбца рабочего листа.
Тот же метод используется для очистки фильтров, примененных к столбцу в таблице. В этом случае метод AutoFilter является членом объекта Range объекта ListObject.
Фильтры и типы данных
Параметры раскрывающегося меню фильтра меняются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.
Я создал отдельные сообщения для каждого из этих типов фильтров. Посты содержат пояснения и примеры кода VBA.
Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо! 🙂
Участник форума Регистрация 01-07-2004 Сообщений 314
Метод ShowAllData возвращает ошибку
Использование 64-разрядной версии Excel 2013.
Я записал фрагмент кода при очистке фильтра.
Полученный код дал мне метод ShowAllData,
поэтому я попытался использовать его в своей процедуре, но получаю сообщение об ошибке:
Есть идеи, что я делаю неправильно?
Спасибо
w
Гуру форума Дата регистрации 19 февраля 2005 г. Расположение Гамбург, Германия MS-Off Ver 2013 на Win10 (настольный компьютер), 2007 на Win10 (ноутбук) Сообщений 8098
Используйте теги кода для показа своего кода: [code] Ваш код здесь [/code]
Пожалуйста, отметьте свой вопрос как решенный, если было предложено решение, которое вам подходит
Участник форума Регистрация 01-07-2004 Сообщений 314
Я немного изменил код по вашему предложению.
Получено это сообщение об ошибке
Ошибка выполнения "1004":
Для этой команды требуется как минимум две строки исходных данных. вы не можете использовать команду для выбора только в одной строке. Попробуйте следующее.
Гуру форума Дата регистрации 19 февраля 2005 г. Расположение Гамбург, Германия MS-Off Ver 2013 на Win10 (настольный компьютер), 2007 на Win10 (ноутбук) Сообщений 8098
может быть так:
Участник форума Регистрация 01-07-2004 Сообщений 314
Та же ошибка.
Это то, что я закодировал ниже, что создает фильтр и скрытые строки, но я не понимаю, почему приведенный выше код не обрабатывает это?
ExportToPDF2 содержит обновленный код
Гуру форума Дата регистрации 19 февраля 2005 г. Расположение Гамбург, Германия MS-Off Ver 2013 на Win10 (настольный компьютер), 2007 на Win10 (ноутбук) Сообщений 8098
вы очищаете содержимое листа Control, вставляете заголовки в первую строку, а затем хотите выполнить расширенный фильтр по отсутствующим данным - мне понятна ошибка времени выполнения, поскольку я не знаю, почему вы хочу сделать это. Обходным решением может быть проверка последней заполненной строки и выполнение фильтрации только в том случае, если количество заполненных строк составляет не менее 3 или более.
Читайте также: