Excel 97 - Autofill & VBA

[Binnit]

Hopefully a simple answer here....

When double clicking on a worksheet cell the formula is copied down where the adjacent left cell is not empty, once it finds an empty cell it stops.

I have recorded this event in a macro:-

Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F710")

As you can see it has completed the range destination "F710" which was fine for this particular case.

What I need to find is the VBA dynamic equivilant of the "destination" cell so that when the macro is run it copies down until it gets to the first empty adjacent cell (the same action as the double click).

Any suggestions?




Happiness is...not getting what you want but wanting what you have already got

 

[Chance1234]

firstly look at range in the vba help files,

also have a look at thread707-1400305



Chance,

F, G + H

 

[Binnit]

Thanks Chance for the helpful directions to xlbo's post which did the trick, I will do the needful and provide him with a star.....

Happiness is...not getting what you want but wanting what you have already got

 

[SkipVought]

Give xlbo's FAQ a vote.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}