Getting a range that includes alternate rows only

[neilbirtles]

Hi,

I am using excel to display a large amount of data (>10,000 lines)
that is exported from a c# program. What i am trying to do is to
colour in alternate rows to make this data easier to read.

I currently have a function that iterates through each alternate row
and colours them one at a time.

for (int colouringIndex = 2; colouringIndex < rowCounter+3;
colouringIndex += 2)
{
Range rowRange = sheet.get_Range("A" +
colouringIndex.ToString(), brLastColumnWanted+

colouringIndex.ToString());
headingRange.EntireRow.Interior.ColorIndex = 1;
}

This is taking ~20 sec. So what i want to do is speed the process up
by getting one Range that covers all the alternate rows that i need to
colour and then set the colour for those rows in one go. Is there a
way to do this in C#? In VBA you can do something like

Range("1:1, "3:3", "5,5").Select
Selection.Interior.ColorIndex = 1;

Whats the C# equivalent?

thanks

Neil

 

[Gary Keramidas]

you can try something like this
Sub test()
Dim rng As Range
Dim y As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
Set rng = ws.Range("A1:B1000")
With rng
For y = 1 To rng.Rows.Count
If y Mod 2 = 1 Then
Range(ws.Cells(y, "A"), ws.Cells(y,
"B")).Interior.ColorIndex = 43
Else
Range(ws.Cells(y, "A"), ws.Cells(y,
"B")).Interior.ColorIndex = 0
End If
Next
End With

End Sub

 

[Gary Keramidas]

and if you want some borders, try this

Sub test()
Dim rng As Range
Dim y As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
Set rng = ws.Range("A1:B1000")
With rng
For y = 1 To rng.Rows.Count
If y Mod 2 = 1 Then
Range(ws.Cells(y, "A"), ws.Cells(y,
"B")).Interior.ColorIndex = 43
Else
Range(ws.Cells(y, "A"), ws.Cells(y,
"B")).Interior.ColorIndex = 0
End If
Next
End With
With rng
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
End With
End Sub

 

[Gord Dibben]

Without code.......................

You could select the 10000 rows and Format>Conditional Formatting>Formula is

=MOD(ROW(),2)=1

Format to a nice color and OK


Gord Dibben MS Excel MVP