Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a range that includes alternate rows only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a range that includes alternate rows only
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a range that includes alternate rows only
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 -- Gary wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a range that includes alternate rows only
Without code.......................
You could select the 10000 rows and FormatConditional FormattingFormula is =MOD(ROW(),2)=1 Format to a nice color and OK Gord Dibben MS Excel MVP On Sat, 19 May 2007 12:43:52 -0400, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
Auto-filter includes too much range | Excel Discussion (Misc queries) | |||
adding range name that includes a sheet name | Excel Programming | |||
=max(range includes #N/As) | Excel Worksheet Functions | |||
How to specify a Range which includes an integer variable | Excel Programming |