macro for copying cells above empty ones

[JRB1024]

I'm having trouble makin a macro for the following:
In column C i have varying entries of numbers or blank cells. If the
cell is blank I need it to go to the nearest cell above it and copy
that number. For example

A1=443
A2=
A3=
A4=558
A5=

I would need A2 and A3 to be filled with 443 and A5 to be filled with
558. please help!

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iEnd As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "" Then
If iEnd = 0 Then iEnd = i
Else
If iEnd > 0 Then
.Cells(i + 1, TEST_COLUMN).Resize(iEnd - i).Value = _
.Cells(i, TEST_COLUMN).Value
iEnd = 0
End If
End If
Next i
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

you can do this without a macro

Select the range
hit CTRL+G (Goto)
Select 'Special'
In specil window select 'Blanks'
now click on OK
Now only blank cells are selected
type formula as = and just hit up arrow and then hit return.
hit CTRL+D, this will copy the formula to the blank cells only
then you can do a copy-paste special values on a safer side.