Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge the same group of cells down a column quickly | Excel Discussion (Misc queries) | |||
Excel- find the last filled cells in column with empty cells | Excel Programming | |||
Empty/Delete Cells in a Column | Excel Programming | |||
Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
How can i copy a group of cells in a row to a column? | Excel Discussion (Misc queries) |