clear cells

[k_babb]

hi i really need some help with a macro i have a massive spread sheet
which has a company name then a cell gap then the next 3 cells have an address then there are different amounts of cells i would like a macro to copy the company name over the address and the blank cells until it finds the next company name is there anyway to do this

 

[Peter_SSs]

Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> InsertCoName()<br>    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> a <SPAN style="color:#00007F">In</SPAN> .SpecialCells(xlCellTypeConstants).Areas<br>            <SPAN style="color:#00007F">If</SPAN> a.Rows.Count > 1 <SPAN style="color:#00007F">Then</SPAN> a.ClearContents<br>        <SPAN style="color:#00007F">Next</SPAN> a<br>        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[k_babb]

i tried it but all the get is #ref in column a

 

[Peter_SSs]

i tried it but all the get is #ref in column a

That may indicate that your data does not start in cell A1. We can't tell from your sample data where it is. Maybe it isn't even in column A? So what else can you tell us about that?