Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Drop down list to Autofill next column | Excel Worksheet Functions | |||
Autofill column from data (code) in column next to it | Excel Worksheet Functions | |||
How do I autofill data in a column? | Excel Discussion (Misc queries) | |||
How to set the autofill options in a column of a input data table | Excel Discussion (Misc queries) | |||
Macro that will autofill a column with data, up to the last row of data in previous c | Excel Programming |