Excel VBA - 高级过滤器 - 突出显示行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20352844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - advanced filter - highlight rows
提问by Griphus
I am trying to use a macro to do an advanced filter on a range with a fixed number of columns but variable number of rows. I would then like to highlight the visible rows and clear the filter.
我正在尝试使用宏对列数固定但行数可变的范围进行高级过滤。然后我想突出显示可见行并清除过滤器。
Through research, I've come up with this code:
通过研究，我想出了这个代码：
ActiveSheet.Range("A2:J" & Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = 65535
Strangely, it was working but stopped when I changed some of the filter criteria. So something is going on that I don't understand. I now get run-time error 1004 "Application-defined or object-defined error."
奇怪的是，它正在工作，但当我更改了一些过滤条件时停止了。所以发生了一些我不明白的事情。我现在收到运行时错误 1004“应用程序定义或对象定义的错误”。
More code / larger snippet:
更多代码/更大的片段：
Sheets("Filters").Activate
Cells.Find(What:="WBS Element", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Set CritRange = Range(ActiveCell, ActiveCell.End(xlDown))
Sheets("Data").Activate
DataRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=CritRange
Cells(1, 1).EntireRow.Font.Bold = True
ActiveSheet.Range("A2:J" & Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = 65535
ActiveSheet.ShowAllData
回答by Griphus
So, I found a solution while putzing. Basically, I took the one line of code and multiplexed it out into several lines for each thing I wanted to do. The problem seemed to come from how I was setting my range... this works for me:
所以，我在putzing时找到了一个解决方案。基本上，我将一行代码多路复用成几行，用于我想做的每一件事。问题似乎来自我如何设置范围......这对我有用：
Range(Cells(2, 1), Cells(Rows.Count, 10)).SpecialCells(xlCellTypeVisible).Interior.Color = 65535
回答by David Zemens
OK try this. There was some problems with the range assignment. You don't need Cells(Rows.Count,1)since you only care about the row # you can just use .Rows.Count.
好的试试这个。范围分配存在一些问题。您不需要，Cells(Rows.Count,1)因为您只关心行 # 您可以只使用.Rows.Count.
I also think it useful to use object variables (like filterRangein the example below). It will make your code a lot easier to interpret as you move forward.
我也认为使用对象变量很有用（如下filterRange例所示）。随着您的前进，这将使您的代码更容易解释。
Sub HighlightRows()
Dim filterRange As Range
With ActiveSheet
'// first define the range
Set filterRange = _
.Range("A2:J" & Rows.Count).End(xlUp).SpecialCells(xlCellTypeVisible)
'// now, manipulate the range as needed
filterRange.EntireRow.Interior.Color = 65535
End With
End Sub
You should be able to modify this to fit within your subroutine, or you can keep this as a stand-alone subroutine and call it by name, like this:
您应该能够修改它以适应您的子例程，或者您可以将其保留为独立子例程并按名称调用它，如下所示：
Sub YourSubRoutine()
Sheets("Filters").Activate
Cells.Find(What:="WBS Element", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Set CritRange = Range(ActiveCell, ActiveCell.End(xlDown))
Sheets("Data").Activate
DataRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=CritRange
Cells(1, 1).EntireRow.Font.Bold = True
'// Call a subroutine to do the highlighting:
Call HighlightRows
ActiveSheet.ShowAllData
End Sub
