Recording macros with autofill

[andyiain]

Hi,

I've oft have occasion to record macros, yet I'm not very good so...

How do you do the equivalent of auto-fill a column by double clicking
the bottom right hand corner of a cell. When I do this in a macro I
record, rather than record the auto-fill of a series of data for the
length of data it puts in the range that should be auto-filled. It
means that if i use the macro again on a sheet with a different amount
of data I'll get missing results of cell errors.

Any help would be appreciated.

Andy

 

[Guest]

I'm afraid that's just the way Excel records that function and you're stuck
unless you want to get into the VB Editor and edit your macro.

Where the recorded macro would have a line like this.....
Selection.AutoFill Destination:=Range("L8:L1055")

You would have to change it to look like this.....
Selection.AutoFill Destination:=Range("L8:L" & _
Cells(Rows.Count, 1).End(xlUp).Row)

If you want to try this, be sure to do so on a copy of your file in case of
things happening that you didn't want to have happen..........

hth
Vaya con Dios,
Chuck, CABGx3

 

[andyiain]

Hi, thanks for the reply,

I modified the macro as suggested but when i ran it i got a dialogue
error box with just the number 400 in it, when i hit help it didn't do
anything. Anyone know what this means?

Regards
Andy

 

[Guest]

The code works fine in my model.........please post your macro.

Vaya con Dios,
Chuck, CABGx3

 

[andyiain]

Hi Chuck,

Thanks for that.

I'm not too familiar (to say the least) with VBA, seems I mucked things
up when I tried to type this in to the Macro.

It is sorted now.

Many thanks for all your help.

Andy

 

[Guest]

Glad you got it working.........thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3