autofill column data

[ALAN EMERY]

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

 

[Tom Ogilvy]

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

 

[ALAN EMERY]

thankyou
you have saved me many hours work

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


if