根据列中的日期隐藏行

I've searched and searched the internet and all of the forums and I've been piecing together code and still can't figure this out. I've tried For loops and For Each loops and still can't get it right. In my sheet, I have all of my dates in Column D. I want to hide rows by month. I want to be able to click a macro button and only show dates in January, or February, or etc.

我搜索并搜索了互联网和所有的论坛,我一直在拼凑代码,但仍然无法解决这个问题。我已经尝试过For循环和For Each循环但仍然无法正确。在我的工作表中,我的所有日期都在D列中。我希望按月隐藏行数。我想能够点击一个宏按钮,只显示一月或二月的日期等。

This is what I currently have:

这就是我目前所拥有的:

Sub January()
'
'
'
Dim cell As Range
For Each cell In Range("Date")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = False
    End If
    If cell.Value < "1/1/2018" Or cell.Value > "1/31/2018" Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub

When I run this, it just hides anything that isn't an empty cell. I've cycled between defining cell as a Range and as a Variant and it's the same either way.

当我运行它时,它只是隐藏任何不是空单元格的东西。我已经在定义单元格作为范围和作为变体之间循环,并且它们都是相同的。

ETA:

It is working now and it took help from everybody. I really appreciate it! Here's what I ended with..

它现在正在工作,它得到了每个人的帮助。对此,我真的非常感激!这就是我结束的......

Sub January()
'
'
'
Dim cell As Range
For Each cell In Range("Date")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = False
    ElseIf cell.Value < CDate("1/1") Or cell.Value > CDate("1/31") Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub

I removed the years from the code so that I don't have to change any coding for future years.

我从代码中删除了多年,以便我不必为将来的年份更改任何编码。

5 个解决方案

#1

Your current setup would qualify all dates as either < or > the respective date comparison.

您当前的设置会将所有日期限定为 <或> 相应的日期比较。

If you are trying to hide rows for January in this code, then you need to use AND instead of OR

如果您尝试在此代码中隐藏1月份的行,则需要使用AND而不是OR

And be sure you use >= & <= to include those first and last dates.

并确保使用> =&<=来包含第一个和最后一个日期。

If cell >= "1/1/2018" AND cell <= "1/31/2018" Then

If you are trying to hide rows not January then your < and > are transposed:

如果您试图隐藏行而不是1月,那么您的 <和> 会被转置:

If cell < "1/1/2018" OR cell > "1/31/2018" Then

#2

Alternative approach: If you've got Excel 2013 or later, simply add a Table Slicer and filter on a MONTH column generated with =DATE(YEAR([@Date]),MONTH([@Date]),1) as shown below:

替代方法:如果您已经使用Excel 2013或更高版本,只需在使用= DATE(年([@日期]),MONTH([@日期]),1)生成的MONTH列上添加表切片器和过滤器,如下所示:

Or otherwise use a PivotTable and a Slicer:

或者使用数据透视表和切片器:

To see how easy it is to set up a PivotTable, see VBA to copy data if multiple criteria are met

要查看设置数据透视表是多么容易,请参阅VBA以在满足多个条件时复制数据

#3

Ultimately, I believe this is the code you're looking for:

最终,我相信这是您正在寻找的代码:

Sub January()
Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Range("Date")
 'If date falls on or after January 1, AND on or before January 31, don't hide the row
  If cell.Value >= CDate("1/1/2018") And cell.Value <= CDate("1/31/2018") Then
    cell.EntireRow.Hidden = False
  Else
   'If the cell doesn't contain anything or isn't in January, hide the row
    cell.EntireRow.Hidden = True
  End If
Next cell

Application.ScreenUpdating = True
End Sub

You need to use And logic, not Or logic. Or logic always returns TRUE unless both expressions are false or there is a null involved. Because of this, the code stopped looking at your logical statement once it evaluated to true since every date you had - I'm assuming - fell after January 1, 2018. This in turn caused the rows to hide unexpectedly.

你需要使用And逻辑,而不是逻辑。或者逻辑始终返回TRUE,除非两个表达式都为false或涉及null。因此,代码一旦评估为真,就会停止查看你的逻辑语句,因为你所拥有的每个日期 - 我假设 - 在2018年1月1日之后下降。这反过来导致行意外隐藏。

Additionally, I would convert the strings you have into dates using CDate. It helps Excel understand what is going on a bit better and makes your code easier to understand to outsiders. Another good practice to work on is adding comments to code. I think we've all learned the hard way by leaving comments out of code at some point or another.

另外,我会使用CDate将您拥有的字符串转换为日期。它有助于Excel了解更好的内容,并使您的代码更容易理解为外人。另一个好习惯是在代码中添加注释。我认为我们已经通过在某些时候将评论从代码中删除而学到了很多困难。

One last thing: if you're planning to have buttons for each month, consider doing one procedure for all of them and having variables populate the date ranges, potentially using input boxes to get the values from the user. It'll save you a lot of headaches if you ever decide to change things up in the future.

最后一件事:如果您计划每个月都有按钮,请考虑为所有这些按钮执行一个过程,并使用变量填充日期范围,可能使用输入框来获取用户的值。如果您决定在将来改变一切,它会为您省去很多麻烦。

#4

Untested, written on mobile. I am just providing an alternative approach which tries to use MONTH and YEAR. Some may find this approach easier to understand.

未经测试,写在手机上。我只是提供了一种尝试使用MONTH和YEAR的替代方法。有些人可能会发现这种方法更容易理解。

Option Explicit

Sub January()

Dim cell As Range

For Each cell In Range("Date")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = False
    Else
    cell.EntireRow.Hidden = (Month(cell.Value) = 1) and (year(cell.Value) = 2018)
End if

Next cell

End sub

#5

I will actually go with Slicers and Table.
But if you call VBA your neat solution then I'd say abandon the loop.
Have nothing against it but if Excel already have the functionality, then use it.
It is like a discount or a promotion that we need to take advantage of.
So instead of loop, why not just filter?

我实际上会选择Slicers和Table。但是,如果你给VBA打电话给你很好的解决方案,那么我会说放弃循环。没有任何反对意见,但如果Excel已经具有该功能,则使用它。这就像我们需要利用的折扣或促销。所以不是循环,为什么不过滤?

Dim lr As Long, r As Range
With Sheet1 '/* sheet where data reside */
    .AutoFilterMode = False '/* reset any filtering already applied */
    lr = .Range("D" & .Rows.Count).End(xlUp).Row '/* get the target cells */
    Set r = .Range("D1:D" & lr) '/* explicitly set target object */
    '/* filter without showing the dropdown, see the last argument set to false */
    r.AutoFilter 1, ">=2/1/2018", xlAnd, "<=2/28/2018", False 
End With

Above is for February of this year, you can tweak it to be dynamic.
You can create separate sub procedure for each month of you can just have a generic one.

以上是今年2月,你可以调整它是动态的。您可以为每个月创建单独的子程序,您可以只使用通用程序。

#1