清洁条件格式（Excel VBA） [英] Clean Conditional Formatting (Excel VBA)

问题描述

如果已经回答了这个问题，我很抱歉，但我无法找到它。这就是我想要的：我们都知道，删除范围，行和列将分割条件格式并使其变得可怕。我想创建一个个人宏：

  1。）在活动表单中搜索所有现有的条件格式
 2.）根据其条件和格式结果识别重复项
 3.）查找所有重复项中最左边的列和最高行
 4.）找到所有重复项中最右边的列和最下面的行
 5.）确定使用这四个值的扩大范围
 6.）记住条件和格式
 7.）删除所有重复项
 8.）在扩展范围内重新创建条件格式
 9.）重复直到没有更多的重复发现
 10）输出在MsgBox 
中删除了多少个重复项$ b $ / code $ 

我有50％的自信，我可以自己做，但我有一种感觉，我需要学习如何使用数组变量。 （其中我完全无知，因此害怕）所以如果有人已经创建了这个，那么我就会你分享你的天才。或者如果有人认为他们可以鞭打这个，我会为您提供创造可能成为如果不是最常用的个人宏用户群体的最常用的工具之一+ Shift + V）。

或者如果没有人或想要，那么也许有几个提示？在这里我可以给我一个骨头！

解决方案

这将删除复制和粘贴行时创建的条件格式化规则的重复集： / p>

  Option Explicit 
 
 Public Sub resetConditionalFormatting（）
 
 Const F_ROW As Long = 2 
 Dim ws As Worksheet，ur As Range，maxCol As Long，maxRow As Long，thisCol As Long 
 Dim colRng As Range，fcCol As Range，fcCount As Long，fcAdr As String 
 
设置ws = ThisWorkbook.ActiveSheet 
设置ur = ws.UsedRange 
 maxRow = ur.Rows.Count 
 maxCol = ur.Columns.Count 
 
 Application.ScreenUpdating = False 
对于每个colRng在ws.Columns 
如果colRng.Column> maxCol然后退出
 thisCol = thisCol + 1 
设置fcCol = ws.Range（ws.Cells（F_ROW，thisCol），ws.Cells（maxRow，thisCol））
使用colRng.FormatConditions 
如果.Count> 0然后
 fcCount = 1 
 fcAdr = .Item（fcCount）.AppliesTo.Address 
 
虽然fcCount< = .Count 
如果.Item（fcCount）。 AppliesTo.Address = fcAdr然后
 .Item（fcCount）.ModifyAppliesToRange fcCol 
 fcCount = fcCount + 1 
 Else 
 .Item（fcCount）.Delete 
 End If 
 Wend 
 
结束如果
结束
下一个
 Application.ScreenUpdating = True 
 End Sub 
  

。

在高层次：

• 通过活动工作表的使用范围的每一列


• 根据地址集确定重复项



• 如果它找到多个集合：

  
  
  
  
  
  • 对于第一个集合，它将AppliesTo范围更新为（firstRow：lastRow）
  
  
  • 删除所有其他集
  
  

（可以在.Delete之后添加重复的计数器陈述）

---

测试文件

初始规则：

复制和粘贴最后2行后，两次：

清理后：

< hr>

注意：

• 有14种不同类型的规则，许多属性不同


• 不是所有类型都有.Formula或.Formula1，甚至是相同的格式属性


• 类型可以在测试文件或这个Microsoft页面

I apologize if this has been answered already but I was unable to find it. Here's what I want: We all know that deleting ranges, rows, and columns will split conditional formatting and make it hideous. I'd like to create a personal macro that:

1.) Searches through all existing Conditional Formatting in the active sheet
2.) Recognizes duplicates based on their condition and format result
3.) Finds the leftmost column and highest row in all duplicates
4.) Finds the rightmost column and lowest row in all duplicates
5.) Determines a broadened Range using those four values
6.) Remembers the condition and format
7.) Deletes all duplicates
8.) Recreates the Conditional Format over the broadened Range
9.) Repeats until no more duplicates are found
10) Outputs how many duplicates were deleted in a MsgBox

I'm 50% confident I could do this myself, but I have a feeling I'll need to learn how to work with array variables. (Of which I'm completely ignorant and thus terrified) So if anyone has already created this, then I beg you to share your genius. Or if anyone thinks they can whip this out, I offer you the chance to create what might become one of if not the most commonly included tool of the entire population of personal macro users (Right up there with Ctrl+Shift+V).

Or if nobody has or wants to, then maybe a few tips??? C'mon throw me a bone here!

解决方案

This removes duplicate sets of conditional formatting rules created when copying and pasting rows:

Option Explicit

Public Sub resetConditionalFormatting()

    Const F_ROW As Long = 2
    Dim ws As Worksheet, ur As Range, maxCol As Long, maxRow As Long, thisCol As Long
    Dim colRng As Range, fcCol As Range, fcCount As Long, fcAdr As String

    Set ws = ThisWorkbook.ActiveSheet
    Set ur = ws.UsedRange
    maxRow = ur.Rows.Count
    maxCol = ur.Columns.Count

    Application.ScreenUpdating = False
    For Each colRng In ws.Columns
        If colRng.Column > maxCol Then Exit For
        thisCol = thisCol + 1
        Set fcCol = ws.Range(ws.Cells(F_ROW, thisCol), ws.Cells(maxRow, thisCol))
        With colRng.FormatConditions
            If .Count > 0 Then
                fcCount = 1
                fcAdr = .Item(fcCount).AppliesTo.Address

                While fcCount <= .Count
                    If .Item(fcCount).AppliesTo.Address = fcAdr Then
                        .Item(fcCount).ModifyAppliesToRange fcCol
                        fcCount = fcCount + 1
                    Else
                        .Item(fcCount).Delete
                    End If
                Wend

            End If
        End With
    Next
    Application.ScreenUpdating = True
End Sub

.

At high level:

• It goes through each column of the used range of the active sheet
• Determines duplicates based on sets of addresses

• If it finds multiple sets:

  • For the first set - it updates the AppliesTo range to (firstRow:lastRow)
  • Deletes all other sets

(a duplicate counter can be added after the .Delete statement)

---

Test file

Initial rules:

After copying and pasting the last 2 rows, twice:

After cleanup:

---

Notes:

• There are 14 different types of rules and many properties are different
• Not all types have .Formula or .Formula1, or even the same formatting properties
• Types can be seen in the test file or this Microsoft page

这篇关于清洁条件格式（Excel VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！