vba Excel：如果该行中没有单元格着色，则隐藏该行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15654458/
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: Hide a row if no cells in that row are colored
提问by user2214690
I have a workbook set up to where Sheet1 is editable due dates in multiple columns (columns C through T) for certain listed names in columns A&B; Rows 1&2 are a header so data input starts on row 3.
我有一个工作簿，其中 Sheet1 在 A&B 列中的某些列出名称的多列（C 列到 T 列）中可编辑截止日期；第 1 行和第 2 行是标题，因此数据输入从第 3 行开始。
Sheet2 is identical using INDIRECT formulas as a protected page with conditional formatting that highlights certain cells either red or yellow if the due date is coming up.
Sheet2 是相同的，使用 INDIRECT 公式作为具有条件格式的受保护页面，如果到期日即将到来，则以红色或黄色突出显示某些单元格。
I'm inexperienced with VBA and have been searching for a macro that meets the following criteria:
我对 VBA 没有经验，一直在寻找符合以下条件的宏：
On Sheet2 only, if the row does not contain any cells that are red or yellow then hide those non-colored rows.
仅在 Sheet2 上，如果该行不包含任何红色或黄色的单元格，则隐藏那些非彩色行。
Any help would be greatly appreciated. I have only found code on hiding rows based on criteria in single columns.
任何帮助将不胜感激。我只找到了基于单列条件隐藏行的代码。
回答by StoriKnow
Here's a little script to get you started. It will loop through each column of each row and check the color of each cell. If any color is found, that row will be skipped. If there are no cells found that have any color, the row will be hidden. In other words, all fully white row will be hidden.
这是一个让你开始的小脚本。它将遍历每一行的每一列并检查每个单元格的颜色。如果找到任何颜色，将跳过该行。如果没有找到具有任何颜色的单元格，该行将被隐藏。换句话说，所有全白行都将被隐藏。
CODE:
代码：
Public Sub HideUncoloredRows()
Dim startColumn As Integer
Dim startRow As Integer
Dim totalRows As Integer
Dim totalColumns As Integer
Dim currentColumn As Integer
Dim currentRow As Integer
Dim shouldHideRow As Integer
startColumn = 1 'column A
startRow = 1 'row 1
totalRows = Sheet2.Cells(Rows.Count, startColumn).End(xlUp).Row
For currentRow = totalRows To startRow Step -1
shouldHideRow = True
totalColumns = Sheet2.Cells(currentRow, Columns.Count).End(xlToLeft).Column
'for each column in the current row, check the cell color
For currentColumn = startColumn To totalColumns
'if any colored cell is found, don't hide the row and move on to next row
If Not Sheet2.Cells(currentRow, currentColumn).Interior.ColorIndex = -4142 Then
shouldHideRow = False
Exit For
End If
Next
If shouldHideRow Then
'drop into here if all cells in a row were white
Sheet2.Cells(currentRow, currentColumn).EntireRow.Hidden = True
End If
Next
End Sub
BEFORE
前
AFTER
后
回答by g8r
row = 1
do
flag = false
col = 1
do
.cells(row,col).select
if selection.interior.colorindex <> vbWhite then flag = true
loop until (col = lastcol) or (flag = true)
if flag then
rows(row).delete
row = row - 1
end if
row = row + 1
loop until row = lastrow
