Excel：如果该行中没有单元格被着色，则隐藏一行 [英] Excel: Hide a row if no cells in that row are colored

问题描述

我有一个工作簿设置在Sheet1可以编辑的到期日期在多列（列C到T）中列A和B中的某些列出的名称;第1行和第2行是一个标题，因此数据输入从第3行开始。

Sheet2是相同的，使用INDIRECT公式作为受条件格式的受保护页面，突出显示某些单元格为红色或黄金，如果到期日期到来。

我没有经验的VBA，并一直在寻找符合以下条件的宏：

仅在Sheet2上，如果该行不包含任何红色或黄色的单元格，则隐藏这些非彩色行。

任何帮助将不胜感激。我只找到基于单列条件隐藏行的代码。

解决方案

这里有一个小脚本让你开始。它将循环遍历每行的每一列，并检查每个单元格的颜色。如果找到任何颜色，该行将被跳过。如果没有找到具有任何颜色的单元格，行将被隐藏。换句话说，全部白色行将被隐藏。

代码：

  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'列A 
 startRow = 1'行1 
 totalRows = Sheet2.Cells（Rows.Count，startColumn）.End（xlUp）.Row 
 
对于currentRow = totalRows To startRow步骤-1 
 shouldHideRow = True 
 totalColumns = Sheet2.Cells（currentRow，Columns.Count）.End（xlToLeft）.Column 
'用于当前行中的每一列，检查单元格color 
对于currentColumn = startColumn到totalColumns 
'如果找到任何有色单元格，不要隐藏该行并移动到下一行
如果不是Sheet2.Cells（currentRow，currentColumn）.Interior.ColorIndex = -4142然后
 shouldHideRow = False 
退出
结束如果
下一个
 
如果shouldHideRow然后
'如果所有单元格都为白色
 Sheet2.Cells（currentRow，currentColumn）.EntireRow.Hidden = True 
 End If 
 Next 
 End Sub 
  

BEFORE

AFTER

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.

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.

I'm inexperienced with VBA and have been searching for a macro that meets the following criteria:

On Sheet2 only, if the row does not contain any cells that are red or yellow then hide those non-colored rows.

Any help would be greatly appreciated. I have only found code on hiding rows based on criteria in single columns.

解决方案

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

这篇关于Excel：如果该行中没有单元格被着色，则隐藏一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！