A simple maco for someone, but not for me.

[Bill Wilson]

I have a long column of numbers, but some of them are
blank. Here is what it looks like:

(Top of Column)
Blank Cell
Blank Cell
478
Blank Cell
Blank Cell
Blank Cell
Blank Cell
Blank Cell
6009

I want to fill the blank cells with the numbers from the
bottom, so it looks like this:

(Top of Column)
478
478
478
6009
6009
6009
6009
6009
6009

See my problem? I forsee a simple little macro which
starts at the bottom number in the column, copies it, and
moves up one cell. If the new cell is blank, it pastes
the number and moves up one more cell, etc. When the new
cell already has a number, it copies that number and
pastes it into the blank cells above it. How do you
write this sucker?

Thank you for your help.

 

[Harald Staff]

Sub tester()
Dim S As String
Dim L As Long
Dim Col As Long

Col = 1 ' A column

For L = Cells(65000, Col).End(xlUp).Row To 1 Step -1
If Cells(L, Col).Formula = "" Then
Cells(L, Col).Formula = S
Else
S = Cells(L, Col).Formula
End If
Next

End Sub

HTH. Best wishes Harald

 

[Tushar Mehta]

A non loop solution that has undergone minimal testing:
Option Explicit

Sub testIt()
Range(Range("a1"), Cells(Rows.Count, 1).End(xlUp)) _
.SpecialCells(xlCellTypeBlanks).Formula = "=a2"
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Patrick Molloy]

you don't need code for this really,
if A1 is the top of the column, then in B1
=IF(A1="",A2,A1)
repeat this down the column.

Otherwise, here's your code:

DIM rw as Long
DIM lastrw as long
lastrw = Range("A65000").End(xlUp).Row
For rw = 1 to lastrw
with Cells(rw,1)
if .Value = "" then
.FormulaR1C1 = "=R[1]C"
end if
end with
Next