Grouping Question; Redux

[ryguy7272]

I posted here a few days ago and Jacob Skaria kindly provided me with a great
idea on how to do some grouping with VBA.

I tweaked the code a bit, and am now working with this:

Sub Grp()
Dim lngRow As Long

Sheets("Sheet1").Select
For lngRow = 9 To Cells(Rows.Count, "B").End(xlUp).Row

If Range("B" & lngRow) <> "" And Left(Range("B" & lngRow), 0) = Space(0) And
Left(Range("B" & lngRow), 2) = Space(2) Then
Range("B" & lngRow - 1 & ":B" & lngRow).Rows.Group
End If

If Range("B" & lngRow) <> "" And Left(Range("B" & lngRow), 2) = Space(2) And
Left(Range("B" & lngRow), 4) = Space(4) Then
Range("B" & lngRow & ":B" & lngRow).Rows.Group
End If

If Range("B" & lngRow) <> "" And Left(Range("B" & lngRow), 4) = Space(4) And
Left(Range("B" & lngRow), 6) = Space(6) Then
Range("B" & lngRow & ":B" & lngRow).Rows.Group
End If

Next lngRow
End Sub

This code mostly works, but not quite because some rown are grouped
inappropriately.

What I’d like to do is show the small minus sign on the row with, say 2
spaces, or 4 spaces, and then group down until the beginning of the next
group, and do the same. Now, it shows the minus sign on the row with, say 2
spaces, or 4 spaces, but it seems to group up, not down. Sorry if it sounds
confusing; it is confusing. Also, if a cell is found with no data at all, I
know this is a totally blank cell; when I encounter a blank cell, I'd like to
offset one row down, and continue the process, until the end of the Range,
defined by lngRow. This searching for blank cells is definitely a problem.
Several 'groups' turn out fine, but one, for example does not work at all.
For instance, in one pattern, I have one cell with two spaces, and then
several cells with four spaces, and I know these cells with four spaces are
grouped under the cell with two spaces. Then, under that, I have a few cells
with two spaces, so these cells are really in another group. Then, below
that, I eventually come to a cell with no spaces, so that would be grouped
accordingly. Does that make sense? I need to go further and further 'into'
the 'groups' and then come back out.

I know it can be done, but there seems to be something wrong with the way I
am evaluating the blanks; sometimes the results are fine, but sometimes the
results are grouped wrong, especially if there is a totally blank cell under
a group of cells.
Does anyone have any suggestions as to how to handle this?


Thanks,
Ryan---

 

[Jacob Skaria]

Hi Ryan.

Two days back I was searching for your original post but could not find it..

As you mentioned it is somewhat confusing. Would you be able to post the
file with the 'original data' and 'what you are looking for' either to a
external website or mail to (e-mail address removed)..Will definitely look
into...Cheers

If this post helps click Yes