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?

 

[Guest]

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

 

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

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]

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