Select multiple rows and apply changes all at once

[JPlankenhorn]

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?

[David Gerstman]

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
>
>

[Die_Another_Day]

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

[JPlankenhorn]

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]

Can anyone help me?

[Die_Another_Day]

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

[JPlankenhorn]

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