Basis question about Group/Ungroup

[SmartGuy]

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

 

[Joel]

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]

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]

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("A14")
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("A14")

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("A14")

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

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.

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]

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.

 

[SmartGuy]

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.