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

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

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)

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)

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.

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.