Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
format many rows depending on record type
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
format many rows depending on record type
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).Pas teSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Next i myR1.AutoFilter End Sub "Gary Schneider" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
format many rows depending on record type
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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).Pas teSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Next i myR1.AutoFilter End Sub "Gary Schneider" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif depending on format | Excel Discussion (Misc queries) | |||
Code Date Format Depending on Computer format | Excel Discussion (Misc queries) | |||
How to delete a set of rows depending on Value | Excel Discussion (Misc queries) | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) | |||
How do I format a row depending on the value of a cell in the row | Excel Discussion (Misc queries) |