Copy down

[edsjr]

I have a large spreadsheet (over 20k rows), in column a there is a company name and then many empty rows next to it. I need something that will fill down until it gets to a new name and then fill down again. see example, column a is original and i need it to be like column b

thanks in advance

Ed.

PS, I can't post attachment....how do I do that?

Here is a link to the file https://1drv.ms/x/s!AlMKyRBHlUsBgcgCRG-tFLFp64rdNg

 

[Fluff]

How about

Sub edsjr()
   With Range("A1", Range("B" & Rows.Count).End(xlUp).Offset(-1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

This site does not allow members to upload files. You either upload to a share site, or you can use one of the add-ins available here to post data direct to the thread https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[edsjr]

I get a runtime error everytime I do it

I'll admit, I don't know much about macros

I threw the sample file on my onedrive account and made it shareable, link in original post

 

[jtakw]

Hi,

1. For your sample, at the Very Last cell in Column A where you want the copy to STOP (i.e. A43), enter Any Value ( i.e. "Stop" )
2. Enter this formula in A2,
=A1
Click Enter, select A2, right click, Copy
3. Select Column A, hit F5, click "Special", Check "Blanks", click "OK"
4. Hit Ctrl and V

You're done.

Delete the value in A43 if desired.

 

[My Aswer Is This]

I ran the code and it worked for me.

 

[edsjr]

what jtakw posted worked like a charm

My Aswer Is This......probably something in my security settings.

Thank you both for the help

 

[My Aswer Is This]

Well that works if you do not mine all those formulas in those cells.
If you look at all the cells that earlier were empty now have a formula in them

what jtakw posted worked like a charm

My Aswer Is This......probably something in my security settings.

Thank you both for the help

 

[jtakw]

what jtakw posted worked like a charm

Glad it worked for you.

 

[edsjr]

Well that works if you do not mine all those formulas in those cells.
If you look at all the cells that earlier were empty now have a formula in them

copy and paste values, formulas gone

 

[My Aswer Is This]

copy and paste values, formulas gone

Yes I knew you could do that.