如何根据两列中的值隐藏Excel中的行 [英] How to hide rows in Excel based on values in two columns
问题描述
我正在尝试自动化一些报告,并遇到一个小小的障碍。我试图根据2个seprate列中的单元格的值在excel工作表上隐藏行。我下面给了一个例子。 (真正的文档在一个给定的时间和几乎任何日期可以包含最多50个名字。)
对于这个例子,我需要隐藏所有没有列A中的值JIM,并且在列B中的日期大于日期(04/05/2014)
AB
JIM 04/05/2014
JIM 04/05/2014
BOB 05/05/2014
BOB 04/05/2014
TED 07/05/2014
TED 04/05/2014
BOB 09/05/2014
JIM 04/05/2014
JIM 11/05/2014
TED 12/05/2014
我正在使用以下代码,但在添加第二个参数时遇到了一些问题。 / p>
Sub Delete_Name_Date()
Dim rng As Range,cell As Range
对于范围内的每个单元格(A1:F15)
如果cell.Value =JIM然后
cell.EntireRow.Hidden = False
否则:cell.EntireRow.Hidden = True
结束如果
下一个
End Sub
谢谢
Adam
尝试这个:
Sub Delete_Name_Date()
Dim cell As Range
对于范围内的每个单元格(A1:A15)
单元格
.EntireRow.Hidden = _
(.Value< ;> JIM和CDate(.Offset(,1).Value)>日期)
结束
下一个
End Sub
可靠性我建议您使用 ThisWorkbook.Sheets(SheetName)。Range(A1:A15)
而不是范围(A1:A15 )
First post here and i am very new to the world of Excel-VBA.
I am trying to automate some reports and have run into a little snag. i am trying to hide rows on an excel worksheet based on the value of cells in 2 seprate columns. i have given given an example below. (The real Document can contain up to 50 names at one given time and pretty much any date.)
For this example i would need to hide all rows that do not have the value JIM in column A and have a greater date than todays date in column B. (04/05/2014)
A B
JIM 04/05/2014
JIM 04/05/2014
BOB 05/05/2014
BOB 04/05/2014
TED 07/05/2014
TED 04/05/2014
BOB 09/05/2014
JIM 04/05/2014
JIM 11/05/2014
TED 12/05/2014
I am using the below code at the minute but ran into some issues when adding a second argument.
Sub Delete_Name_Date()
Dim rng As Range, cell As Range
For Each cell In Range("A1:F15")
If cell.Value = "JIM" Then
cell.EntireRow.Hidden = False
Else: cell.EntireRow.Hidden = True
End If
Next
End Sub
Any help would be great.
Thanks
Adam
Try this one:
Sub Delete_Name_Date()
Dim cell As Range
For Each cell In Range("A1:A15")
With cell
.EntireRow.Hidden = _
(.Value <> "JIM" And CDate(.Offset(, 1).Value) > Date)
End With
Next
End Sub
also for reliability I suggest you to use ThisWorkbook.Sheets("SheetName").Range("A1:A15")
instead Range("A1:A15")
这篇关于如何根据两列中的值隐藏Excel中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!