format many rows depending on record type

[Gary Schneider]

Hi all,

I would like to format a sheet with many rows (>10000) by VBA. Each row
has a record type stored in one of the columns. For each record type,
there is a template row. All rows of a kind shall then be formated like
this template row.

Therefore I'm using "Pastespecial Paste:=xlPasteFormats". That works
fine, but it's too slow (2 or three minutes for 4000 rows, but I have
even more rows). Is there a better & faster way? I tried styles as
well, but the colums of the rows are formatted using many different
colors, borders, number formats etc. so that won't work.

Here's the code as it is now:
For i = 20 To 30000
Select Case Range("M" & i).Value ' this contains the record
type
Case "a"
Rows(1).Copy
Rows(i).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Case "b"
Rows(2).Copy
Rows(i).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.... and so on ....
Else
Exit For
End If
Next i

TIA
gary

 

[Bernie Deitrick]

Gary,

Don't do it row by row. Assuming that you have a column heading in M19, then you can use
autofilters to select each type all at once (within a loop to handle each of your format types).
Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub GaryFormat()
Dim myR1 As Range
Dim myR2 As Range
Dim myVals() As Variant
Dim i As Integer

myVals = Array("a", "b", "c") ' increase to reflect your actual data set
Set myR1 = Range(Range("M19"), Range("M65536").End(xlUp))
Set myR2 = Range(Range("M20"), Range("M65536").End(xlUp))

On Error Resume Next

For i = LBound(myVals) To UBound(myVals)
myR1.AutoFilter Field:=1, Criteria1:=myVals(i)
Rows(i).Copy
myR2.EntireRow.SpecialCells(xlCellTypeVisible).PasteSpecial _
Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next i

myR1.AutoFilter
End Sub

 

[Bernie Deitrick]

Gary,

I'm sorry - I forgot to mention that this code requires

Option Base 1

at the top of the codemodule.

HTH,
Bernie
MS Excel MVP