数据点更改后插入行 [英] insert rows after a data point changes

问题描述

我有一个数据集，如下所示：

 
 This1 GH 
 This2 GH 
 This3 GH 
这4个BR 
这5个BR 
当数据点改变时，即GH到BR我想要excel插入换行符。所以最终的数据看起来像这样。 
 
 This1 GH 
 This2 GH 
 This3 GH 
 
 This4 BR 
这5个BR 
 
这6个VB 
 
任何想法如何完成？我认为一个负的迭代循环将工作。但是我不知道在这种情况下excel如何处理行操作。 
解决方案
最快的方式（ TRIED AND TESTED ）
  Option Explicit 
 
 Sub Sample（）
 Dim aCell As Range，bCell As Range 
 Dim ExitLoop As Boolean 
 
 With Sheets（Sheet1）
 .Columns（A：B）。小计GroupBy：= 2，功能：= xlCount，TotalList：= Array（2），_ 
替换：= True，PageBreaks：= False，SummaryBelowData：= True 
 
设置aCell = .Cells.Find（什么：=Count，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder = = xlByRows，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）
 
如果不是aCell是没有
设置bCell = aCell 
 .Rows（aCell.Row）.ClearContents 
 Do While ExitLoop = False 
设置aCell = .Cells.FindNext（之后：= aCell）
 
如果不是aCell是没有，然后
如果aCell.Address = bCell.Address然后退出Do 
 .Rows（aCell.Row）.ClearContents 
 Else 
 ExitLoop = True 
如果
循环
结束如果
 
 .Cells.RemoveSubtotal 
结束
结束子
  
我假设第1行有标题。
 
 
 
 宏观行动 
 
 
 
  
 
I have a data set that looks like this:
This1    GH
This2    GH
This3    GH
This4    BR
This5    BR
This6    VB
when the data point changes, i.e. "GH" to "BR" I want excel to insert a line break. so that the finalized data looks like this. 
This1    GH
This2    GH
This3    GH

This4    BR
This5    BR

This6    VB
any idea how this would be done? i think that a negative iterating for loop would work. but i don't know how excel would handle row manipulation in this case. 
解决方案
The fastest way to do it (TRIED AND TESTED)
Option Explicit

Sub Sample()
    Dim aCell As Range, bCell As Range
    Dim ExitLoop As Boolean

    With Sheets("Sheet1")
        .Columns("A:B").Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

        Set aCell = .Cells.Find(What:=" Count", LookIn:=xlValues, _
                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                     MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            Set bCell = aCell
            .Rows(aCell.Row).ClearContents
            Do While ExitLoop = False
                Set aCell = .Cells.FindNext(After:=aCell)

                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do
                    .Rows(aCell.Row).ClearContents
                Else
                    ExitLoop = True
                End If
            Loop
        End If

        .Cells.RemoveSubtotal
    End With
End Sub
I am assuming that Row 1 has headers.

MACRO IN ACTION



                        
这篇关于数据点更改后插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！