日期筛选器通过Excel VBA隐藏所有单元格 [英] Date Filter Hiding All Cells Through Excel VBA

问题描述

我正在使用以下内容过滤掉特定的日期，但是它隐藏了所有单元格，并且没有保留在所需的数据中.手动完成后，它可以正常工作，当我将范围更改为最后一行( $ AL1210 )时，它也可以工作，但是由于我每天都会粘贴新数据，因此该范围会增加.

I'm using the below to filter out specific dates however it hides all cells and does not leave in the required Data. When completed manually it works fine it also works when I change the range to the last row($AL1210) however this range will increase as I will be pasting in new data daily.

我正在尝试寻找一种范围的解决方案，以理想地自动增加到最后一行(尽管我已经尝试了其他答案并获得了相同的结果，因为我不明白为什么过滤器不起作用，有人帮我吗?

I am trying to find a solution for the range to ideally automatically increase to last row(although I've tried this from other answers and get the same result, as I don't understand why the filter does not work, can anyone help me with this?

Sub Auto_Filter()
    `Auto_Filter Macro
    `This Auto Filters all data for years 2017 & 2018, this also sorts to newest date first
    Keyboard Shortcut: Ctrl+Shift+A
    Sheets("Paste Data").Select
    ActiveSheet.Range("$A$1:$AL$10000").AutoFilter Field:=9, Criteria1:= _
        ">=01/01/2017", Operator:=xlAnd, Criteria2:="<=31/12/2018"
    ActiveWorkbook.Worksheets("Paste Data").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Paste Data").AutoFilter.Sort.SortFields.Add Key:= _
        Range("I1:I10000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Paste Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

推荐答案

在代码中使用"yyyy/mm/dd"日期格式– JK2017

use "yyyy/mm/dd" date format in your code – JK2017

工程谢谢

这篇关于日期筛选器通过Excel VBA隐藏所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！