Excel VBA macro to group the rows according to the state
source link: https://www.codesd.com/item/excel-vba-macro-to-group-the-rows-according-to-the-state.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Excel VBA macro to group the rows according to the state
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK