Excel宏分组 [英] Excel Macro to group

问题描述

我不知道一个宏是否是这个问题的最佳解决方案，但是我希望对行进行分组并放入一个if语句来使它更容易一些。

我想看到标有A的所有内容被分组到上面的B中。表中的加号表示分组加在Excel中添加。

我尝试将这些表格格式化为表格，但遇到一些问题，我希望分解文本的方式显示了A在上述B中的主要思路。

表之前：

  B 88888 876 
 A 8765 
 A 8765 
 A 8765 
 B 
 B 88888 8765 
 A 5768 
 A 8765 
 B 88888 578 
 A 578 
 B 
 B 88888 7658 
 A 5768 
 A 6578 
 A 8765 
 A 5768 
 B 88888 5768 
 A 5678 
  

表后：

 code> + B 88888 876 
 B 
 + B 88888 8765 
 + B 88888 578 
 B 
 + B 88888 7658 
 + B 88888 5768 
  

解决方案

..

  Option Explicit 
 
 Public Sub customGroups（）
 Const FR As Long = 2'first row（exclude header）
 Dim ws As Worksheet，As As Range，grp As Range 
 
设置ws = ActiveSheet 
与ws 
与.UsedRange 
 .Rows.ClearOutline 
设置ur = .Offset（FR  -  1，0）.Resize（.Rows .Count  - （FR  -  1），.Columns.Count）
 .AutoFilter字段：= 1，Criteria1：== A *，运算符：= xlFilterValues 
对于每个grp In .SpecialCells（xlCellTypeVisible ）.Areas 
如果grp.Row> FR  -  1然后grp.Rows.Group 
下一个
结束
 .AutoFilterMode = False 
带.Outline 
 .ShowLevels RowLevels：= 1 
。 SummaryRow = xlAbove 
结束
结束
结束子
  

。

I'm not sure if a macro would be the best solution to this problem, but I am looking to group rows and put in an if statement to make it a little easier.

I would like to see everything marked A to be grouped up into the above B. With the plus in the after table signifying the grouping plus that is added in Excel.

I tried formatting these as a table but ran into some issues I hope the way I broke up the text shows the main idea with the A's rolling up into the above B.

Before table:

B   88888   876  
A       8765  
A       8765  
A       8765  
B       
B   88888   8765  
A       5768  
A       8765  
B   88888   578  
A       578  
B       
B   88888   7658  
A       5768  
A       6578  
A       8765  
A       5768  
B   88888   5768  
A       5678  

After table:

+   B   88888   876  
    B       
+   B   88888   8765  
+   B   88888   578  
    B       
+   B   88888   7658  
+   B   88888   5768  

解决方案

This will group all rows staring with "A..."

Option Explicit

Public Sub customGroups()
    Const FR As Long = 2    'first row (exclude header)
    Dim ws As Worksheet, ur As Range, grp As Range

    Set ws = ActiveSheet
    With ws
        With .UsedRange
            .Rows.ClearOutline
            Set ur = .Offset(FR - 1, 0).Resize(.Rows.Count - (FR - 1), .Columns.Count)
            .AutoFilter Field:=1, Criteria1:="=A*", Operator:=xlFilterValues
            For Each grp In .SpecialCells(xlCellTypeVisible).Areas
                If grp.Row > FR - 1 Then grp.Rows.Group
            Next
        End With
        .AutoFilterMode = False
        With .Outline
            .ShowLevels RowLevels:=1
            .SummaryRow = xlAbove
        End With
    End With
End Sub

.

这篇关于Excel宏分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！