Copying Down Until There Is a Value Question

[CowDaddy]

Let's say cell A1 has a value of "ice cream".

No cells in column A have a value until A350, which has the value of "moonpies."

Then, no cells in column A have a value until A5265, which has a value of "twinkies."

What I would like is for cells A1 to A349 to have the value of ice cream. And "ice cream" needs to stop at cell A349. It might be nice if when it stopped, it picked up the next value and did the same thing, but I am more than fine with doing it again manually. I have to do this only about 15 times but that 15 times is over 18,000 rows.

Thanks!

 

[AD_Taylor]

Try this macro in a standard code module:
Please make a copy of your workbook before testing - macros cannot be undone.

Make sure to change the Range("A1:A18000") to the last row you want it to run till.

Sub FillInValues()

Dim checkRng As Range
Set checkRng = Range("A1:A18000")

Dim currCell As Range
Dim currStr As String
currStr = ""

For each currCell in checkRng.Cells

If currCell.Value = "" Then

currCell.Value = currStr

Else

currStr = currCell.Value

Next currCell

End Sub

Not greatly efficient but the best way that came off the top of my head

 

[CowDaddy]

Thanks, but I get the message,

"compile error, next without for"

 

[Peter_SSs]

This manual method should get you close, if not completely there.

1. Select column A by clicking its heading label.

2. Press F5|Special...|Blanks|OK

3. Press the = key then the up arrow key then confirm with Ctrl+Enter

4. (Optional) If you want to replace the formulas with their values, select column A again and Copy then Paste Special (Values)

 

[villy]

Try this macro in a standard code module:
Please make a copy of your workbook before testing - macros cannot be undone.

Make sure to change the Range("A1:A18000") to the last row you want it to run till.

Sub FillInValues()
 
Dim checkRng As Range
Set checkRng = Range("A1:A18000")
 
Dim currCell As Range
Dim currStr As String
currStr = ""
 
For each currCell in checkRng.Cells
 
If currCell.Value = "" Then
 
currCell.Value = currStr
 
Else
 
currStr = currCell.Value
 
Next currCell
 
End Sub

Not greatly efficient but the best way that came off the top of my head

Add EndIf before Next currCell

 

[AD_Taylor]

Thanks, but I get the message,

"compile error, next without for"

Yes sorry, like I said wrote completely off the top of my head. Should be:

Sub FillInValues()

Dim checkRng As Range
Set checkRng = Range("A1:A18000")

Dim currCell As Range
Dim currStr As String
currStr = ""

For each currCell in checkRng.Cells

If currCell.Value = "" Then

currCell.Value = currStr

Else

currStr = currCell.Value

[B][COLOR=Red]End If[/COLOR][/B]

Next currCell

End Sub

 

[Peter_SSs]

If you do want a vba approach, you might wish to also try this considerably faster code. It assumes that column B can be used to determine how far down to copy the last value in column A.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Fill_Blanks()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a<br><br>    lr = Range("B" & Rows.count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1:A" & lr)<br>        a = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> a(i, 1) = "" <SPAN style="color:#00007F">Then</SPAN> a(i, 1) = a(i - 1, 1)<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[CowDaddy]

Hey, thanks everybody! I really appreciate it. It saved me a bunch of time.

I used Peter's manual method at work, but I have checked out the other methods.

Thanks a lot. Really.

Do you guys know if that code cd Mr. Excel has for cell--does it have examples like this with descriptive names?

CowDad

 

[Peter_SSs]

Do you guys know if that code cd Mr. Excel has for cell--does it have examples like this with descriptive names?

CowDad

Sorry, don't know but perhaps you could ask about that in the MrExcel's Products: Books, CDs, Podcasts Forum.