Posted to microsoft.public.excel.programming
|
|
More efficient way?
Thanks for all the info Don. I'll try adding making the formula row
directly above the autofill range. The small change to my workbook
structure will be well worth it if autofill speeds the code up! If not, oh
well.....the 'ol college try! Thanks again for all your help!!
"Don Guillett" wrote in message
...
Yep. Never hurts to highlight the word and touch the F1 key. Amazing what
you get. Maybe you could clear/hide the interveing rows after the fill.
AutoFill Method
See Also Applies To Example Specifics
Performs an autofill on the cells in the specified range. Variant.
expression.AutoFill(Destination, Type)
expression Required. An expression that returns one of the objects in
the
Applies To list.
Destination Required Range object. The cells to be filled. The
destination
must include the source range.
Type Optional XlAutoFillType. Specifies the fill type.
XlAutoFillType can be one of these XlAutoFillType constants.
xlFillDays
xlFillFormats
xlFillSeries
xlFillWeekdays
xlGrowthTrend
xlFillCopy
xlFillDefault default
xlFillMonths
xlFillValues
xlFillYears
xlLinearTrend
If this argument is xlFillDefault or omitted, Microsoft Excel
selects
the most appropriate fill type, based on the source range.
Example
This example performs an autofill on cells A1:A20 on Sheet1, based on the
source range A1:A2 on Sheet1. Before running this example, type 1 in cell
A1
and type 2 in cell A2.
Set sourceRange = Worksheets("Sheet1").Range("A1:A2")
Set fillRange = Worksheets("Sheet1").Range("A1:A20")
sourceRange.AutoFill Destination:=fillRange
--
Don Guillett
SalesAid Software
"Steph" wrote in message
...
Hi Don,
Thanks for the new suggestion. I edited your piece of code to fit my
range,
and I got an Autofill method of range class failed error. Is that
because
my formula is in row 2, and I don't want the autofill to start until row
5?
My code is :
Set shtarray = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
For Each sh In shtarray
Range("A2:EC2").AutoFill Destination:=Range("A5:EC" &
Data.Range("B65536").End(xlUp).Row)
With sh.Range("A5:EC" & Data.Range("B65536").End(xlUp).Row)
.Value = .Value
End With
Next sh
Thanks again Don!!
"Don Guillett" wrote in message
...
try using the autofill idea
Range("f2:i2").AutoFill Destination:=Range("f2:i6")
--
Don Guillett
SalesAid Software
"Steph" wrote in message
...
Thanks for the response Don. That does exactly what I wanted done.
But
unfortunately, it's no faster than a similar For Next loop I
have....as
a
matter of fact, the timing of the 2 procedures are almost identical!
Oh
well, Thanks again!
"Don Guillett" wrote in message
...
I don't know if this is what you want but this will copy row 2
formulas
in
each sheet down to the last cell in col B of each worksheet.
Sub fc()
Set shtarray = Sheets(Array("LMU", "Kit", "SMLC"))
For Each sh In shtarray
With sh
x = .Cells(Rows.Count, "B").End(xlUp).Row
.Rows(2).Copy .Rows("2:" & x)
End With
Next
End Sub
--
Don Guillett
SalesAid Software
"Steph" wrote in message
...
Hi everyone. Was hoping someone had some ideas on how to speed
up
the
following code. The base code was started using the
macro-recorder.
I
selected several sheets using the ctl key,
then copied row 2 with formulas in it (each sheet has a row of
formulas
in
the same row), then pasted it to a range. It works....just
slowly.
I'm
running a 2.4G processor with 2.0G of memory...and it still
takes
forever!
Granted, each sheet is being populated with 13,000 cells, but I
still
didn't
think it would take this long. Have a better method?? Thanks!
Sub Forecast()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC
Cab",
"Serv
Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))
shtarray.Select
Sheets("LMU").Activate
Range("A2:EC2").Copy
Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row)
frng.PasteSpecial Paste:=xlPasteFormulas
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
|