How to group empty cells in a column
 

Hello,

I am new in Office programming, and I wonder how to select in the best way
the groups of "used" empty cells from the first column A.

A B C
1 X X
2 X
--------
3 X
4 X X
--------
5 X
6 X
7 X X
8 X X
--------
9 X
10 X
11 X
12 X
..............

Well, I would like to group the rows, leaving the A column value as group
name.

I need to identify the lower valued cell in all columns, and the non empty
cells in the "A" column.

Is there a possibility to do it "quick" in .NET (C#)?
CODE SNIPPET
// The following code opens an existing workbook
string workbookPath = @"C:\MyFile.xls"; // Add your own path here
Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, XlPlatform.xlWindows, "", true,
false, 0, true, false, false);

// The following gets the Worksheets collection
Sheets excelSheets = excelWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet excelWorksheet =

(Microsoft.Office.Interop.Excel.Worksheet)excelShe ets.get_Item(1);

excelWorksheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;

Range excelCell = (Range)excelWorksheet.get_Range("A1",
Missing.Value);
excelCell.get_End(XlDirection.xlDown).get_Address ;

string downAddress = excelCell.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);

excelCell = excelWorksheet.get_Range("A1", downAddress);


Probably, I must use the get_Range and get_End functions; also the UsedRange
property should give me the entire range, with all the empty cells, but not
quite sure what to do from here...

How to group empty cells in a column
 

set emptycells = Columns("A:A").SpecialCells(xlCellTypeBlanks)

You may get one extra cell at row after the last row of your data.

"serhio" wrote:

Hello,

I am new in Office programming, and I wonder how to select in the best way
the groups of "used" empty cells from the first column A.

A B C
1 X X
2 X
--------
3 X
4 X X
--------
5 X
6 X
7 X X
8 X X
--------
9 X
10 X
11 X
12 X
.............

Well, I would like to group the rows, leaving the A column value as group
name.

I need to identify the lower valued cell in all columns, and the non empty
cells in the "A" column.

Is there a possibility to do it "quick" in .NET (C#)?
CODE SNIPPET
// The following code opens an existing workbook
string workbookPath = @"C:\MyFile.xls"; // Add your own path here
Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, XlPlatform.xlWindows, "", true,
false, 0, true, false, false);

// The following gets the Worksheets collection
Sheets excelSheets = excelWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet excelWorksheet =

(Microsoft.Office.Interop.Excel.Worksheet)excelShe ets.get_Item(1);

excelWorksheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;

Range excelCell = (Range)excelWorksheet.get_Range("A1",
Missing.Value);
excelCell.get_End(XlDirection.xlDown).get_Address ;

string downAddress = excelCell.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);

excelCell = excelWorksheet.get_Range("A1", downAddress);


Probably, I must use the get_Range and get_End functions; also the UsedRange
property should give me the entire range, with all the empty cells, but not
quite sure what to do from here...

How to group empty cells in a column
 

Thanks, Joel

in the situation bellow:
___A__B
_1 [ ][ ]
_2 [ ][ ]
_3 [ ][ ]
_4 [X ][ ]
5 [ ][X ]
6 [ ][X ]
7 [ ][X ]
8 [ ][X ]
_9 [X ][ ]
10 [ ][X ]
11 [ ][X ]
12 [ ][X ]
13 [ ][X ]
14 [X ][ ]
15 [ ][X ]
16 [ ][X ]
17 [ ][X ]
18 [ ][ ]

I have


excelWorksheet.UsedRange.get_Address(Missing.Value , Missing.Value,
XlReferenceStyle.xlA1, Missing.Value, Missing.Value)
=
"$A$4:$B$17"

How can I select the first column in this range?
I've tried:
excelWorksheet.UsedRange.Columns[0, 0]
but
'excelWorksheet.UsedRange.Columns[0, 0]' threw an exception of type
'System.Runtime.InteropServices.COMException' object
{System.Runtime.InteropServices.COMException}

After select the first column, I have to group the rows with empty cells:

excelWorksheet.UsedRange.[?firts
Column?].SpecialCells(XlCellType.xlCellTypeBlanks, Missing.Value).Group(??)

can you guide me in this a little bit? thanks


"Joel" wrote:

set emptycells = Columns("A:A").SpecialCells(xlCellTypeBlanks)

You may get one extra cell at row after the last row of your data.

"serhio" wrote:

Hello,

I am new in Office programming, and I wonder how to select in the best way
the groups of "used" empty cells from the first column A.

A B C
1 X X
2 X
--------
3 X
4 X X
--------
5 X
6 X
7 X X
8 X X
--------
9 X
10 X
11 X
12 X
.............

Well, I would like to group the rows, leaving the A column value as group
name.

I need to identify the lower valued cell in all columns, and the non empty
cells in the "A" column.

Is there a possibility to do it "quick" in .NET (C#)?
CODE SNIPPET
// The following code opens an existing workbook
string workbookPath = @"C:\MyFile.xls"; // Add your own path here
Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, XlPlatform.xlWindows, "", true,
false, 0, true, false, false);

// The following gets the Worksheets collection
Sheets excelSheets = excelWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet excelWorksheet =

(Microsoft.Office.Interop.Excel.Worksheet)excelShe ets.get_Item(1);

excelWorksheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;

Range excelCell = (Range)excelWorksheet.get_Range("A1",
Missing.Value);
excelCell.get_End(XlDirection.xlDown).get_Address ;

string downAddress = excelCell.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);

excelCell = excelWorksheet.get_Range("A1", downAddress);


Probably, I must use the get_Range and get_End functions; also the UsedRange
property should give me the entire range, with all the empty cells, but not
quite sure what to do from here...

How to group empty cells in a column
 

I just tried to group the "empty" rows at once, in this way:

excelWorksheet.UsedRange.SpecialCells(XlCellType.x lCellTypeBlanks,
Missing.Value).Group(Missing.Value, Missing.Value, Missing.Value,
Missing.Value);

but I've obtained:
"
The command you chose cannot be performed with multiple selections.
Select a single range and click the command again.
"

System.Runtime.InteropServices.ExternalException
{System.Runtime.InteropServices.COMException}


"Joel" wrote:

set emptycells = Columns("A:A").SpecialCells(xlCellTypeBlanks)

You may get one extra cell at row after the last row of your data.