autofill column data
 

hi all
am having no joy trying to write a vba macro that will scan column F if it
finds something it autofills that data down until the next piece of data
then autofill down that data this needs to be repeated until bottom of sheet
which is row 45,656( hence the need to use vba).
thanks in advance

autofill column data
 

Assuming you don't already have formulas in column F

actually, you can select column F and do

edit=goto=special and select Blanks

assume the first blank cell selected is F3

go to the formula bar and put in the formula

=F2 (the cell above the first selected/blank cell)

then do Ctrl+enter rather than Enter

This will fill all your "holes".

Now select Column F and do Edit=Copy, then Edit=PasteSpecial and select
Values. this will replace the formulas with the hard coded
Values.

in code
dim rng as Range, rng1 as Range
On error resume Next
set rng = Columns(6).SpecialCells(xlBlanks)
On error goto 0
if not rng is nothing then
rng.Formula = "=" & rng(1).offset(-1,0).Address(0,0)
set rng1 = Range(rng(1),rng(1).End(xldown))
rng1.Formula = rng1.Value
End If


--
Regards,
Tom Ogilvy


"ALAN EMERY" wrote in message
...
hi all
am having no joy trying to write a vba macro that will scan column F if
it
finds something it autofills that data down until the next piece of data
then autofill down that data this needs to be repeated until bottom of
sheet
which is row 45,656( hence the need to use vba).
thanks in advance

autofill column data
 

thankyou
you have saved me many hours work
"Tom Ogilvy" wrote in message
...
Assuming you don't already have formulas in column F

actually, you can select column F and do

edit=goto=special and select Blanks

assume the first blank cell selected is F3

go to the formula bar and put in the formula

=F2 (the cell above the first selected/blank cell)

then do Ctrl+enter rather than Enter

This will fill all your "holes".

Now select Column F and do Edit=Copy, then Edit=PasteSpecial and select
Values. this will replace the formulas with the hard coded
Values.

in code
dim rng as Range, rng1 as Range
On error resume Next
set rng = Columns(6).SpecialCells(xlBlanks)
On error goto 0
if not rng is nothing then
rng.Formula = "=" & rng(1).offset(-1,0).Address(0,0)
set rng1 = Range(rng(1),rng(1).End(xldown))
rng1.Formula = rng1.Value
End If


--
Regards,
Tom Ogilvy


"ALAN EMERY" wrote in message
...
hi all
am having no joy trying to write a vba macro that will scan column F
if
it
finds something it autofills that data down until the next piece of data
then autofill down that data this needs to be repeated until bottom of
sheet
which is row 45,656( hence the need to use vba).
thanks in advance