在VBA中对行进行分组 [英] Grouping Rows in VBA

问题描述

我下面的代码似乎不起作用.本质上，rngList指的是Excel中定义的名称范围，该名称范围长约500行，每n行数都有文本(在有文本的500行中大约有32行).我正在尝试转到非空白单元格(通过模仿Excel中的ctrl + down命令).

I have the code below that doesn't seem to be working. Essentially, rngList refers to a defined name range in Excel that is about 500 rows long and every n number of rows there is text (there are approximately 32 rows out of the 500 that have text). I am trying to go to the non-blank cells (by mimicking the ctrl + down command in Excel).

我正在检查它们是否为空白，以及是否要将其分组.如果不是空白，我想检查左边的单元格，如果它是0，我也想对它进行分组.我现在拥有的代码实质上是在尝试执行此操作，但是我收到以下错误:

I am checking to see if they are blank, and if they are I want to group that cell. If it is not blank, I want to check the cell to the left and if it is 0, I also want to group it. The code I have now is essentially trying to do this but I am receiving the error below:

Group Method of Range Class Failed

然后继续突出显示以下行:

It then goes on to highlight the following line:

Selection.Rows.Group

让我们说不是对空白行进行分组，而是对其中具有1的行进行分组.这样，crtl + down实际上将转到该单元，而不是最后一行.

Let's say instead of grouping rows that are blank, I want to group rows that have 1 in them. That way the crtl + down will actually go to that cell rather than the last row.

非常感谢您的帮助！

代码如下:

rngList.Cells(1).Select
    i = 0

    Do While i < 32
        i = i + 1
        If Selection.Value = "" Then
            Selection.Rows.Group
        Else
            Selection.End(xlToLeft).Select
                If Selection.Value <> 0 Then
                    Selection.Rows.ClearOutline
                End If
        End If
        Selection.End(xlToRight).Select
        Selection.End(xlDown).Select

    Loop

推荐答案

尽管这篇文章年代久远，但我认为我会为可能偶然发现它的人投入两美分.希望我能正确理解您的问题.这是我收集的内容:

Despite the age of this post, I thought I'd throw in my two cents for anyone who might stumble upon it. I hope I understand your question correctly. Here's what I've gathered:

目标:对于关注列中的每一行，根据条件对行进行分组.

Goal: For every row in the column of interest, group rows based on a criteria.

条件:唯一的rows in the group是不带任何值(空白，空，空)或具有一个值，并具有一个具有值的相邻单元格(直接位于左侧)的的0.唯一的rows not in the group是那些 not 为空白并且相邻单元格为 not 0的.

Criteria: The only rows in the group are those that either have no value (blank, null, empty) OR have a value AND have a neighboring cell (directly to the left) that has a value of 0. The only rows not in the group are those that are not blank and have a neighboring cell that is not 0.

以下是一些示例数据:

注意:范围B1:B12组成的命名范围为rngList，就像OP所说的那样.

Note: the Range B1:B12 makeup the named range rngList, like the OP says they have.

运行宏之前的数据:

运行宏后的数据-未分组:

运行宏后的数据-分组已崩溃:

处理此问题的代码:

要使此代码起作用:在VBE(Visual Basic编辑器)中，打开包含要分组的数据的工作表(还包含命名范围rngList)并粘贴此代码，然后运行宏.

To make this code work: In the VBE (Visual Basic Editor), open the worksheet that contains the data to group (also contains the named range rngList) and paste this code, then run the macro.

注意:尽管我相信代码本身的编写方式可以解释其自身，但是添加了注释以更详细地解释某些部分(例如，变量名有意义并且逻辑很有意义).

Note: The comments are added to explain certain parts in further detail, though I believe the code itself is written in a way that can explain itself (e.g. variable names are meaningful and logic makes sense).

Public Sub GroupCells()
    Dim myRange As Range
    Dim rowCount As Integer, currentRow As Integer
    Dim firstBlankRow As Integer, lastBlankRow As Integer
    Dim currentRowValue As String
    Dim neighborColumnValue As String

    'select range based on given named range
    Set myRange = Range("rngList")
    rowCount = Cells(Rows.Count, myRange.Column).End(xlUp).Row

    firstBlankRow = 0
    lastBlankRow = 0
    'for every row in the range
    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, myRange.Column).Value
        neighborColumnValue = Cells(currentRow, myRange.Column - 1).Value

        If (IsEmpty(currentRowValue) Or currentRowValue = "") Then
            'if cell is blank and firstBlankRow hasn't been assigned yet
            If firstBlankRow = 0 Then
                firstBlankRow = currentRow
            End If
        ElseIf Not (IsEmpty(currentRowValue) Or currentRowValue = "") Then
            'if the cell is not blank and its neighbor's (to the left) value is 0,
            'and firstBlankRow hasn't been assigned, then this is the firstBlankRow
            'to consider for grouping
            If neighborColumnValue = 0 And firstBlankRow = 0 Then
                firstBlankRow = currentRow
            ElseIf neighborColumnValue <> 0 And firstBlankRow <> 0 Then
                'if firstBlankRow is assigned and this row has a value with a neighbor
                'who isn't 0, then the cell one row above this one is to be considered
                'the lastBlankRow to include in the grouping
                lastBlankRow = currentRow - 1
            End If
        End If

        'if first AND last blank rows have been assigned, then create a group
        'then reset the first/lastBlankRow values to 0 and begin searching for next
        'grouping
        If firstBlankRow <> 0 And lastBlankRow <> 0 Then
            Range(Cells(firstBlankRow, myRange.Column), Cells(lastBlankRow, myRange.Column)).EntireRow.Select
            Selection.Group
            firstBlankRow = 0
            lastBlankRow = 0
        End If
    Next
End Sub

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