Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basis question about Group/Ungroup
Hi all:
My dev environmnet: office 2007 ,VS 2007, c#. I want to group rows (say from row 2 to row 7) in a worksheet, how can i do. Also, how could i know group by rows or by columns ? Consider i am a novice to excel, please give me information as deatil as possible. Useful links are welcome. thanks in advaned. Ding Li |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basis question about Group/Ungroup
Group is the wrong term. Group refers to pictures or shapes that you wantt
to put together. All you need to do is set a Range to the Rows or columns you want set MyRange = Rows("1:5") or StartRow = 1 EndRow = 5 set MyRange = Rows(StartRow & ":" & EndRow) "SmartGuy" wrote: Hi all: My dev environmnet: office 2007 ,VS 2007, c#. I want to group rows (say from row 2 to row 7) in a worksheet, how can i do. Also, how could i know group by rows or by columns ? Consider i am a novice to excel, please give me information as deatil as possible. Useful links are welcome. thanks in advaned. Ding Li |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basis question about Group/Ungroup
Yes, thanks Joel. You answered part of my question.
As i have mentioned before, i am novice on excel and still i am confused how to get the Rows ? I mean i have a worksheet like: Worksheet ws = (Worksheet)wb.Worksheets[1]; how to get the rows? i have tried Range r = (Range)ws.Rows[1,7]; it only return Range, not Rows. More details will be greatly helpful. Thanks in advanced. "Joel" wrote: Group is the wrong term. Group refers to pictures or shapes that you wantt to put together. All you need to do is set a Range to the Rows or columns you want set MyRange = Rows("1:5") or StartRow = 1 EndRow = 5 set MyRange = Rows(StartRow & ":" & EndRow) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basis question about Group/Ungroup
Here are some sytaxs for VBA. Notice when I set a variable to a Range object
(Range,cells,rows,columns) I use SET. There is also a difference between ROW and ROWS (COLUMNS and COLUMNS). Set MyRange = Sheets("Sheet1").Range("A1") Set MyRange = Sheets("Sheet1").Range("A1:D4") Set MyRows = Sheets("Sheet1").Rows("1:7") 'columns can be addresssed by letter or numbers Set MyColumns = Sheets("Sheet1").Columns("$A:$D") Set MyColumns = Sheets("Sheet1").Columns("1:4") 'There is a slight difference between sheets and worksheets 'sheets include all type of pages in excel 'Worksheets only are the formula sheets and not graphs Set MyRange = WorkSheets("Sheet1").Range("A1") Set MyRange = WorkSheets("Sheet1").Range("A1:D4") Sheets and Worksheets can be address by the name or the 'Index Number where they are in the workbook Set MyRange = WorkSheets(1).Range("A1") Set MyRange = WorkSheets(3).Range("A1:D4") To get Colun number of row number FirstRow = WorkSheets(1).Range("D4").Row FirstCol = WorkSheets(1).Range("D4").Column Using value is sometimes optional. If you have a cell with a formula value will get the value and not the formula notice when getting value I'm refereing to 1 cell. a = WorkSheets(1).Range("A1") a = WorkSheets(1).Range("A1").value b = WorkSheets(1).Range("D4") b = WorkSheets(1).Range("D4").value You can use cells instead of Range when you have a column number instead of letter a = WorkSheets(1).cells(1,1) b = WorkSheets(1).cells(4,4) or use the column letter a = WorkSheets(1).cells(1,"A") b = WorkSheets(1).cells(4,"4") You can refer to a range of cells by the index or the offset for i = 1 to 9 for j = (i + 1) to 10 if Range("A5")(i,"D") = Range("A5")(j,"D") then 'compares two cell on adjacent rows end if next j next i This is a better way of doing the ame thing for i = 1 to 9 for j = (i + 1) to 10 if Range("A5").offset(i,"D") = Range("A5").offset(j,"D") then 'compares two cell on adjacent rows end if next j next i "SmartGuy" wrote: Yes, thanks Joel. You answered part of my question. As i have mentioned before, i am novice on excel and still i am confused how to get the Rows ? I mean i have a worksheet like: Worksheet ws = (Worksheet)wb.Worksheets[1]; how to get the rows? i have tried Range r = (Range)ws.Rows[1,7]; it only return Range, not Rows. More details will be greatly helpful. Thanks in advanced. "Joel" wrote: Group is the wrong term. Group refers to pictures or shapes that you wantt to put together. All you need to do is set a Range to the Rows or columns you want set MyRange = Rows("1:5") or StartRow = 1 EndRow = 5 set MyRange = Rows(StartRow & ":" & EndRow) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basis question about Group/Ungroup
Thanks Joel, for your code samples and your kindly reply.
However, i am developing on c#, so it is different. For example: in VBA: WorkSheets("Sheet1").Range("A1") . In c#: object WorkSheets["Sheet1"] does not have method/property Range() Can you help me with some c# code samples or web links? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basis question about Group/Ungroup
I have done, and i would like to share it to all:
Question: how to group/ungroup rows in worksheet? Answer: //Suppose you have a worksheet like: Worksheet ws = (Worksheet)wb.Worksheets[1]; // set a range from row 1 to row 5; Range x = (Range)ws.Rows["1:5", Type.Missing]; // group them x.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing); // default the group is not hide, but you can change the status: x.Hidden = true; ---------------------------------------------------------- If you want to group by column, change code Range x = (Range)ws.Rows["1:5", Type.Missing]; to Range x = (Range)ws.Columns[Type.Missing,"1:5"]; I guess it will work.. :) I have not try that yet. Thanks Joel's code, it helped me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Group and Ungroup | Excel Discussion (Misc queries) | |||
Group and ungroup | Excel Programming | |||
group&ungroup | Charts and Charting in Excel | |||
group/ungroup | Excel Programming | |||
Help with Group/Ungroup | Excel Discussion (Misc queries) |