Variable Autofill range
 

Hi,
I'm creating a macro that autofills down formulas from the
top row. The problem is that the list changes from day to
day, and I can't have it pre-filled to a set number
because it affects the pivot table I'm doing.

Is there a code for setting up the macro so that it fills
to the end of whatever list there is?
Ex:

1 2 3 formu1 formu2
2 3 4
3 4 5
4 5 6
5 6 7
6 7 8
7 8 9
8 9 0

The macro needs to copy the formula down to the end of
however many rows are on the list. The macro could run on
a list of 400 rows, or it could run off a list of 375
rows, etc.

Any ideas???
-Kate

Variable Autofill range
 

One way:


Range("D1:E1").AutoFill _
Destination:=Range("D1:E" & _
Range("A" & Rows.Count).End(xlUp).Row), _
Type:=xlFillDefault

In article ,
"Kate" wrote:

Hi,
I'm creating a macro that autofills down formulas from the
top row. The problem is that the list changes from day to
day, and I can't have it pre-filled to a set number
because it affects the pivot table I'm doing.

Is there a code for setting up the macro so that it fills
to the end of whatever list there is?
Ex:

1 2 3 formu1 formu2
2 3 4
3 4 5
4 5 6
5 6 7
6 7 8
7 8 9
8 9 0

The macro needs to copy the formula down to the end of
however many rows are on the list. The macro could run on
a list of 400 rows, or it could run off a list of 375
rows, etc.

Any ideas???
-Kate

Variable Autofill range
 

Kate

You can use End(xlDown) on column C to get the last row. Like this

Range("d1").AutoFill Range("d1", Range("c1").End(xlDown).Offset(0, 1))

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Kate" wrote in message
...
Hi,
I'm creating a macro that autofills down formulas from the
top row. The problem is that the list changes from day to
day, and I can't have it pre-filled to a set number
because it affects the pivot table I'm doing.

Is there a code for setting up the macro so that it fills
to the end of whatever list there is?
Ex:

1 2 3 formu1 formu2
2 3 4
3 4 5
4 5 6
5 6 7
6 7 8
7 8 9
8 9 0

The macro needs to copy the formula down to the end of
however many rows are on the list. The macro could run on
a list of 400 rows, or it could run off a list of 375
rows, etc.

Any ideas???
-Kate