Showalldata vba excel ошибка

I have just experienced the same problem. After some trial-and-error I discovered that if the selection was to the right of my filter area AND the number of shown records was zero, ShowAllData would fail.

A little more context is probably relevant. I have a number of sheets, each with a filter. I would like to set up some standard filters on all sheets, therefore I use some VBA like this

Sheets("Server").Select
col = Range("1:1").Find("In Selected SLA").Column
ActiveSheet.ListObjects("Srv").Range.AutoFilter Field:=col, Criteria1:="TRUE"

This code will adjust the filter on the column with heading «In Selected SLA», and leave all other filters unchanged. This has the unfortunate side effect that I can create a filter that shows zero records. This is not possible using the UI alone.

To avoid that situation, I would like to reset all filters before I apply the filtering above. My reset code looked like this

Sheets("Server").Select
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Note how I did not move the selected cell. If the selection was to the right, it would not remove filters, thus letting the filter code build a zero-row filter. The second time the code is run (on a zero-row filter) ShowAllData will fail.

The workaround is simple: Move the selection inside the filter columns before calling ShowAllData

Application.Goto (Sheets("Server").Range("A1"))
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

This was on Excel version 14.0.7128.5000 (32-bit) = Office 2010

Итог: узнайте, как очистить все фильтры и фильтры в одном столбце с помощью макросов VBA. Включает примеры кода для регулярных диапазонов и таблиц 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

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

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

Bottom line: Learn how to clear all filters, and filters on a single column with VBA macros.  Includes code examples for regular ranges and Excel Tables.

Skill level: Intermediate

Download the File

The Excel file that contains the code can be downloaded below.  This file contains code for filtering different data types and filter types.  Please see my article on The Ultimate Guide to AutoFilters in VBA for more details.

Clear All Filters from a Range

We use the ShowAllData method to clear all filters applied to a range.

This is the same as clicking the Clear button on the Data tab of the ribbon (keyboard shorcut: Alt, A, C)

Only one filter range can be applied to a worksheet, so we are actually clearing the filters on the sheet.

Sub Clear_All_Filters_Range()

  'To Clear All Fitlers use the ShowAllData method for
  'for the sheet.  Add error handling to bypass error if
  'no filters are applied.  Does not work for Tables.
  On Error Resume Next
    Sheet1.ShowAllData
  On Error GoTo 0
  
End Sub

ShowAllData Method Error

If there are no filters are applied to any column, then the ShowAllData method will raise an error.  It’s a Run-time ‘1004′ error with the description: Method ‘ShowAllData’ of object ‘_Worksheet’ failed.

The On Error Resume Next line will bypass that error.  On Error GoTo 0 resets that so errors are raised in any lines of code below.

Note: When the ShowAllData method is referenced as a member of the sheet, it does NOT clear filters that are applied to Excel Tables (ListObjects) unless a cell is selected in the Table.  Therefore, it’s best to use the code below for Tables.

Clear All Filters from an Excel Table

To clear all filters on an Excel Table (ListObject) we also use the ShowAllData method.  In this case, ShowAllData is a member of the AutoFilter property of the ListObject object.

Sub Clear_All_Filters_Table()

Dim lo As ListObject
  
  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  
  'Clear All Filters for entire Table
  lo.AutoFilter.ShowAllData

End Sub

Clear All Filter from All Tables on a Sheet

The code above will only clear filters for a single Table.  We can loop through the Tables on the sheet to clear all filters from each Table.

Sub Clear_All_Table_Filters_On_Sheet()

Dim lo As ListObject
  
  'Loop through all Tables on the sheet
  For Each lo In Sheet1.ListObjects
  
    'Clear All Filters for entire Table
    lo.AutoFilter.ShowAllData
    
  Next lo

End Sub

Clear Filters on a Single Column

To clear filters on a single column we use the AutoFilter method. We only reference the Field parameter and set the value to the number of the column we want to clear.

Sub Clear_Column_Filter_Range()
  
  'To clear the filter from a Single Column, specify the
  'Field number only and no other parameters
  Sheet1.Range("B3:G1000").AutoFilter Field:=4

End Sub

The Field is the column number of the range the filters are applied to, NOT the column number of the worksheet.

The same technique is use to clear filters applied to a column in a Table.  In this case the AutoFilter method is a member of the Range object of the ListObject.

Sub Clear_Column_Filter_Table()

Dim lo As ListObject
  
  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  
  'Clear filter on Single Table Column
  'by specifying the Field parameter only
  lo.Range.AutoFilter Field:=4
  
End Sub

Filters & Data Types

The filter drop-down menu options change based on what type of data is in the column.   We have different filters for text, numbers, dates, and colors.  This creates A LOT of different combinations of Operators and Criteria for each type of filter.

I created separate posts for each of these filter types.  The posts contain explanations and VBA code examples.

• How to Filter for Blank & Non-Blank Cells
• How to Filter for Text with VBA
• How to Filter for Numbers with VBA
• How to Filter for Dates with VBA
• How to Filter for Colors & Icons with VBA

The file in the downloads section above contains all of these code samples in one place.  You can add it to your Personal Macro Workbook and use the macros in your projects.

Please leave a comment below with any questions or suggestions.  Thanks! 🙂

This thread is ancient, but I wasn’t happy with any of the given answers, and ended up writing my own. I’m sharing it now:

We start with:

  Sub ResetWSFilters(ws as worksheet)
             If ws.FilterMode Then   
     ws.ShowAllData   
     Else   
     End If  
    'This gets rid of "normal" filters - but tables will remain filtered
    For Each listObj In ws.ListObjects 
               If listObj.ShowHeaders Then   
                    listObj.AutoFilter.ShowAllData
                    listObj.Sort.SortFields.Clear    
               End If     
       Next listObj
        'And this gets rid of table filters
        End Sub

We can feed a specific worksheet to this macro which will unfilter just that one worksheet. Useful if you need to make sure just one worksheet is clear. However, I usually want to do the entire workbook

Sub ResetAllWBFilters(wb as workbook)
  Dim ws As Worksheet  
  Dim wb As Workbook  
  Dim listObj As ListObject    

       For Each ws In wb.Worksheets  
          If ws.FilterMode Then 
          ws.ShowAllData  
          Else   
          End If   
 'This removes "normal" filters in the workbook - however, it doesn't remove table filters           
   For Each listObj In ws.ListObjects 
        If listObj.ShowHeaders Then   
             listObj.AutoFilter.ShowAllData 
             listObj.Sort.SortFields.Clear    
        End If     
   Next listObj

        Next   
'And this removes table filters. You need both aspects to make it work.  
    End Sub

You can use this, by, for example, opening a workbook you need to deal with and resetting their filters before doing anything with it:

Sub ExampleOpen()
Set TestingWorkBook = Workbooks.Open("C:Intel......") 'The .open is assuming you need to open the workbook in question - different procedure if it's already open
Call ResetAllWBFilters(TestingWorkBook)
End Sub

The one I use the most: Resetting all filters in the workbook that the module is stored in:

Sub ResetFilters()
      Dim ws As Worksheet  
      Dim wb As Workbook  
      Dim listObj As ListObject  
       Set wb = ThisWorkbook  
       'Set wb = ActiveWorkbook
       'This is if you place the macro in your personal wb to be able to reset the filters on any wb you're currently working on. Remove the set wb = thisworkbook if that's what you need
           For Each ws In wb.Worksheets  
              If ws.FilterMode Then 
              ws.ShowAllData  
              Else   
              End If   
     'This removes "normal" filters in the workbook - however, it doesn't remove table filters           
       For Each listObj In ws.ListObjects 
            If listObj.ShowHeaders Then   
                 listObj.AutoFilter.ShowAllData 
                 listObj.Sort.SortFields.Clear    
            End If     
       Next listObj

            Next   
'And this removes table filters. You need both aspects to make it work.  
    End Sub

This thread is ancient, but I wasn’t happy with any of the given answers, and ended up writing my own. I’m sharing it now:

We start with:

  Sub ResetWSFilters(ws as worksheet)
             If ws.FilterMode Then   
     ws.ShowAllData   
     Else   
     End If  
    'This gets rid of "normal" filters - but tables will remain filtered
    For Each listObj In ws.ListObjects 
               If listObj.ShowHeaders Then   
                    listObj.AutoFilter.ShowAllData
                    listObj.Sort.SortFields.Clear    
               End If     
       Next listObj
        'And this gets rid of table filters
        End Sub

We can feed a specific worksheet to this macro which will unfilter just that one worksheet. Useful if you need to make sure just one worksheet is clear. However, I usually want to do the entire workbook

Sub ResetAllWBFilters(wb as workbook)
  Dim ws As Worksheet  
  Dim wb As Workbook  
  Dim listObj As ListObject    

       For Each ws In wb.Worksheets  
          If ws.FilterMode Then 
          ws.ShowAllData  
          Else   
          End If   
 'This removes "normal" filters in the workbook - however, it doesn't remove table filters           
   For Each listObj In ws.ListObjects 
        If listObj.ShowHeaders Then   
             listObj.AutoFilter.ShowAllData 
             listObj.Sort.SortFields.Clear    
        End If     
   Next listObj

        Next   
'And this removes table filters. You need both aspects to make it work.  
    End Sub

You can use this, by, for example, opening a workbook you need to deal with and resetting their filters before doing anything with it:

Sub ExampleOpen()
Set TestingWorkBook = Workbooks.Open("C:Intel......") 'The .open is assuming you need to open the workbook in question - different procedure if it's already open
Call ResetAllWBFilters(TestingWorkBook)
End Sub

The one I use the most: Resetting all filters in the workbook that the module is stored in:

Sub ResetFilters()
      Dim ws As Worksheet  
      Dim wb As Workbook  
      Dim listObj As ListObject  
       Set wb = ThisWorkbook  
       'Set wb = ActiveWorkbook
       'This is if you place the macro in your personal wb to be able to reset the filters on any wb you're currently working on. Remove the set wb = thisworkbook if that's what you need
           For Each ws In wb.Worksheets  
              If ws.FilterMode Then 
              ws.ShowAllData  
              Else   
              End If   
     'This removes "normal" filters in the workbook - however, it doesn't remove table filters           
       For Each listObj In ws.ListObjects 
            If listObj.ShowHeaders Then   
                 listObj.AutoFilter.ShowAllData 
                 listObj.Sort.SortFields.Clear    
            End If     
       Next listObj

            Next   
'And this removes table filters. You need both aspects to make it work.  
    End Sub