Excel VBA宏根据条件对行进行分组 [英] Excel VBA macro to group rows based on condition

问题描述

我正在尝试创建一个基于列A中是否有值对行进行分组的宏。某些没有值的单元格可能仍然有一个空文本字符串，因此最好使用像长度大于2作为分组的条件，而不仅仅是空白。应用宏的范围将是第3行到数据集的最后一行（或者如果需要定义范围，则通过行3000将足够）。例如，如果A4具有值，并且A10具有值，则行5到9应该成为一个组。我发现一些代码只是Googling，但我无法应用它，所以我宁愿从头开始。感谢提前！

I am trying to create a macro that groups rows based on whether or not there is a value in column A. Some cells without a value may still have a null text string, so it would be best to use something like the length being greater than 2 as the condition for grouping rather than just blanks. The range for applying the macro would be row 3 through the last row of the data set (or if the range needs to be defined, through row 3000 would be sufficient). For example, if A4 had a value, and A10 had a value, then rows 5 through 9 should become a group. I found some code just Googling around, but I couldn't apply it right, so I'd rather just start from scratch. Thanks in advance!

推荐答案

尝试这个
适用于我，如果空单元格为空白

try this out works for me if the empty cells are blanks

sub ashGrp()

Dim rng As Range
Dim blankRange As Range
Dim grp As Range
Set rng = Range("a3", Cells(Rows.Count, 1).End(xlUp))
Set blankRange = rng.SpecialCells(xlCellTypeBlanks)

For Each grp In blankRange
    grp.Rows.Group
Next

end sub

如果您需要对文本或空白进行分组，那么该工会代码将会做到这一点。

if you need to group either text or blanks then this union code will do the trick

Sub ashGrp()

    Dim rng As Range
    Dim blankRange As Range
    Dim grp As Range
    Dim txtRange As Range
    Dim unionRange As Range

    Set rng = Range("a3", Cells(Rows.Count, 1).End(xlUp))
    Set blankRange = rng.SpecialCells(xlCellTypeBlanks)
    Set txtRange = rng.SpecialCells(xlCellTypeConstants, xlTextValues)
    Set unionRange = Union(blankRange, txtRange)

    For Each grp In unionRange
    grp.Rows.Group
    Next


End Sub

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