Showalldata vba excel ошибка

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Range(«A5:AY5»).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(«B5»).Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Select

End Sub

Наверное, не самое изящное решение, но и оно, в принципе, устроило бы.

Но обнаружил баг: если в таблице был выставлен какой-то фильтр, копирование и вставка «только значений» происходит некорректно. Не буду описывать подробно; думаю, вы понимаете почему так происходит и без меня =)
Я начал думать, как решить проблему.
В тот же макрос вставил оператор, раскрывающий все фильтры перед копированием. Получилось так:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveSheet.ShowAllData
Range(«A5:AY5»).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(«B5»).Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Select

End Sub

Содержание

1. Скачать файл
2. Очистить все фильтры из диапазона
3. Ошибка метода ShowAllData
4. Очистить все фильтры из таблицы Excel
5. Очистить все фильтры во всех таблицах на листе
6. Очистить фильтры в одной колонке
7. Фильтры и типы данных

Sub Clear_All_Filters_Range()

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

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

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

End Sub

Sub Clear_Column_Filter_Table()

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

    wbk.Activate
    Set Criteria = Sheets("Sheet1").Cells(i, 1)

    Set rng = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, 4))

    wb.Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter, but it crashes on this line

    Selection.AutoFilter

    Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

    rng.Copy

    Range("$BC$2:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial

This question is related to
excel
vba
autofilter

AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllData will still run, throwing an error (because there is no actual filtering).

I had the same issue and got it working with

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

This seems to prevent ShowAllData from running when there is no actual filter applied but with AutoFilterMode turned on.

The second catch Or ActiveSheet.FilterMode should catch advanced filters

The simple way to avoid this is not to use the worksheet method ShowAllData

Autofilter has the same ShowAllData method which doesn’t throw an error when the filter is enabled but no filter is set

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

The error ShowAllData method of Worksheet class failed usually occurs when you try to remove an applied filter when there is not one applied.

I am not certain if you are trying to remove the whole AutoFilter, or just remove any applied filter, but there are different approaches for each.

To remove an applied filter but leave AutoFilter on:

If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

The rationale behind the above code is to test that there is an AutoFilter or whether a filter has been applied (this will also remove advanced filters).

To completely remove the AutoFilter:

ActiveSheet.AutoFilterMode = False

In the above case, you are simply disabling the AutoFilter completely.

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

I am also the same problem. I think the reason are,

1) When my activecell is within the table, «ActiveSheet.ShowAllData» can be work.
2) When my activecell not within the table,
«ActiveSheet.ShowAllData» cannot work.Using this code, ActiveSheet.ListObjects(«Srv»).Range.AutoFilter Field:=1 can clear the filter.

This will work. Define this, then call it from when you need it. (Good for button logic if you are making a clear button):

Sub ResetFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

Add this code below. Once turns it off, releases the filter. Second time turns it back on without filters.

Not very elegant, but served my purpose.

ActiveSheet.ListObjects("MyTable").Range.AutoFilter

'then call it again?
ActiveSheet.ListObjects("MyTable").Range.AutoFilter