Group Data Based on Blank Cell value in column

[mrmmickle1]

I would like to group data based on blank cell values in column B. I want to group cells by counting up from the last active value in column B and then grouping the data below this blank cell. I would like for the groups to end up collapsed at the end of the macro. The data varies in length from day to day. Around 5000 rows. The groups would be based on customers. So a collapsed data set would be ideal. YOu could uncollapse/open them to view the detail. My data looks like this:

Excel 2010
	A	B	C	D	E	F	G
1	Code	Company Name	WO Number	WO Type	Ref Number	Tail Number	Status
2	AAR ACS-NY C/O AAR CORP
3	AARAL	AAR ACS-NY C/O AAR CORP	U065907	Internal	311017816/9112547		CLOSED
4	AARAL	AAR ACS-NY C/O AAR CORP	U067452-28	Internal	312001928/9115687		Closed
5	AARAL	AAR ACS-NY C/O AAR CORP	U067452-2	Internal	312001928/9115687		Closed
6	AARAL	AAR ACS-NY C/O AAR CORP	U065908	Internal	311017816/9112547		CLOSED
7	AEROLINEAS ARGENTINAS
8	AEARG	AEROLINEAS ARGENTINAS	U065467	External	31105356		Closed
9	AEARG	AEROLINEAS ARGENTINAS	U066128	External	31105894		Closed
10	AEARG	AEROLINEAS ARGENTINAS	U066127	External	31105893		Closed
11	AEARG	AEROLINEAS ARGENTINAS	U065904	External	31105715		Closed
12	USA JET AIRLINES, INC
13	USAJE	USA JET AIRLINES, INC	AN20139	External	R0066612		Approved
14	USAJE	USA JET AIRLINES, INC	AN20436	External	R0067550		Quote
15	USAJE	USA JET AIRLINES, INC	AN20198	External	R0066839		Closed
16	USAJE	USA JET AIRLINES, INC	AN20087	External	R0066419		QC
17	USAJE	USA JET AIRLINES, INC	U065242	External	R0064547		Closed
18	PIEDMONT AIRLINES
19	USPAI	PIEDMONT AIRLINES	MN50403	External	8320095018R		Closed
20	WERNER AERO SERVICES LLC
21	WERNC	WERNER AERO SERVICES LLC	AN20373-9	Internal	19088		Quote
22	WERNC	WERNER AERO SERVICES LLC	AN20373-11	Internal	19088		QC
Sheet2

I would like the end result to look like this:

Excel 2010
	A	B	C	D	E	F	G
1	Code	Company Name	WO Number	WO Type	Ref Number	Tail Number	Status
2	AAR ACS-NY C/O AAR CORP
3	AEROLINEAS ARGENTINAS
4	USA JET AIRLINES, INC
5	PIEDMONT AIRLINES
6	WERNER AERO SERVICES LLC
Sheet2

 

[Miracle00]

It is hard to directly group your blank cell, but you can insert a new row and copy the row that contains blank cell, finally delete the oringinal cell, I use an .NET Excel component to perform the codefirst you can vie my oringinal sheet below as the first picture:

use below code:
namespace NETExcelGroup
{
class Program
{
static void Main(string[] args)
{
//Create a new workbook
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\DatatableSample.xls");
Worksheet sheet = workbook.Worksheets[0];

sheet.InsertRow(3);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A9:E9"], sheet.Range["A3:E3"], true);
sheet.DeleteRow(9);

sheet.InsertRow(5);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A13:E13"], sheet.Range["A5:E5"], true);
sheet.DeleteRow(13);

sheet.InsertRow(6);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A17:E17"], sheet.Range["A6:E6"], true);
sheet.DeleteRow(17);

sheet.InsertRow(7);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A2:E2"], sheet.Range["A7:E7"], true);
sheet.DeleteRow(2);

//Save the file
workbook.SaveToFile("Sample.xls");

//Launch the file
System.Diagnostics.Process.Start("Sample.xls");
}
}
}

 

[mrmmickle1]

It is hard to directly group your blank cell, but you can insert a new row and copy the row that contains blank cell, finally delete the oringinal cell, I use an .NET Excel component to perform the codefirst you can vie my oringinal sheet below as the first picture:

use below code:
namespace NETExcelGroup
{
class Program
{
static void Main(string[] args)
{
//Create a new workbook
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\DatatableSample.xls");
Worksheet sheet = workbook.Worksheets[0];

sheet.InsertRow(3);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A9:E9"], sheet.Range["A3:E3"], true);
sheet.DeleteRow(9);

sheet.InsertRow(5);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A13:E13"], sheet.Range["A5:E5"], true);
sheet.DeleteRow(13);

sheet.InsertRow(6);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A17:E17"], sheet.Range["A6:E6"], true);
sheet.DeleteRow(17);

sheet.InsertRow(7);
sheet.GridLinesVisible = true;
sheet.Copy(sheet.Range["A2:E2"], sheet.Range["A7:E7"], true);
sheet.DeleteRow(2);

//Save the file
workbook.SaveToFile("Sample.xls");

//Launch the file
System.Diagnostics.Process.Start("Sample.xls");
}
}
}

I tried to view your sheet and work with your code but I was unsuccessful. I am not familiar with .net and am just a novice at excel so this is probably why I was unable to get the results I wanted. Thank you for taking the time to look this problem over for me. I appreciate your work. I was able to come up with a solution through other means.

I found this code on an old thread that is a little simpler that I was able to get to work:

Sub GroupData()

Dim i As Integer, LastRow As Integer
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To LastRow
    If Not Left(Cells(i, 2), 3) = "" Then
        Cells(i, 2).EntireRow.Group
    End If
Next i
End Sub

Now that I have been able to group all of my data. How do I collapse all groups without having to manually do it?