Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
I am working on a VB6 app that is creating an Excel report. I load the data onto the spreadseet using an array. I would then like to modify the properties of the first 5 columns of every fourth row to have a bottom border. I am able to do this with a loop that applies the change, however, it is kind of slow. I was wondering if there is a way to loop through the table, select the rows I want to change and then apply that change to the selected set of rows? -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
I can't give you an exact answer, but have you looked at the columns or rows
properties and have you designated a range? David "JPlankenhorn" wrote: I am working on a VB6 app that is creating an Excel report. I load the data onto the spreadseet using an array. I would then like to modify the properties of the first 5 columns of every fourth row to have a bottom border. I am able to do this with a loop that applies the change, however, it is kind of slow. I was wondering if there is a way to loop through the table, select the rows I want to change and then apply that change to the selected set of rows? -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
Have you tried not selecting the cells?
For i = 1 to YourMaxRow Step 4 Range("A1:E1").Offset(i,0). With Range("A1:E1").Offset(i,0).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next i Select cells as I understand slows down macros considerably. Charles JPlankenhorn wrote: I am working on a VB6 app that is creating an Excel report. I load the data onto the spreadseet using an array. I would then like to modify the properties of the first 5 columns of every fourth row to have a bottom border. I am able to do this with a loop that applies the change, however, it is kind of slow. I was wondering if there is a way to loop through the table, select the rows I want to change and then apply that change to the selected set of rows? -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
This is actually a Visual Basic 6 program that I am writing, not a macro. I am not overly familiar with the Excel object model, so I am not sure what the properties all do. Here is an example of what I am doing: For i = 14 To intRow Step 4 With xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 5)) ..Borders(xlEdgeBottom).LineStyle = xlContinuous ..Borders(xlEdgeBottom).Weight = xlThin ..Borders(xlEdgeBottom).ColorIndex = xlAutomatic End With Next What I would like to see is something like: For i = 14 To intRow Step 4 xlSheet.Row(i).FlagAsSelected Next With xlSheet.SelectedRows ..Borders(xlEdgeBottom).LineStyle = xlContinuous ..Borders(xlEdgeBottom).Weight = xlThin ..Borders(xlEdgeBottom).ColorIndex = xlAutomatic End With Thanks. -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
Can anyone help me? -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
Try this:
With xlSheet.Range("A" & i & ":E" & i) .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlEdgeBottom).ColorIndex = xlAutomatic Range(.Offset(-3, 0), .Address).Copy End With Range("A" & i + 1 & ":E" & intRow).PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False That should be extremely efficient. Give it a try and let me know Charles JPlankenhorn wrote: This is actually a Visual Basic 6 program that I am writing, not a macro. I am not overly familiar with the Excel object model, so I am not sure what the properties all do. Here is an example of what I am doing: For i = 14 To intRow Step 4 With xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 5)) .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlEdgeBottom).ColorIndex = xlAutomatic End With Next What I would like to see is something like: For i = 14 To intRow Step 4 xlSheet.Row(i).FlagAsSelected Next With xlSheet.SelectedRows .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlEdgeBottom).ColorIndex = xlAutomatic End With Thanks. -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select multiple rows and apply changes all at once
Charles, Thanks for the sample. I tried it and it is much faster than what I was doing. I can't say I understand everything going on here though. Can you explain what is going on in the copy and the paste lines? Thanks, Jim -- JPlankenhorn ------------------------------------------------------------------------ JPlankenhorn's Profile: http://www.excelforum.com/member.php...o&userid=37567 View this thread: http://www.excelforum.com/showthread...hreadid=571974 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select multiple rows not next to each other | Excel Worksheet Functions | |||
select multiple rows and format | Excel Discussion (Misc queries) | |||
How do I apply a formula to multiple rows at the same time? | Excel Worksheet Functions | |||
Apply a simple formula to multiple rows? | New Users to Excel | |||
how to select multiple rows using vba | Excel Programming |