Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping Question; Redux
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 Id 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping Question; Redux
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting whole Protected 2003 REDUX | Excel Discussion (Misc queries) | |||
Grouping Question | Excel Programming | |||
Data Validation redux | Excel Programming | |||
EXCEL file corruption redux | Excel Programming | |||
Grouping Question | Excel Worksheet Functions |