macro for copying cells above empty ones
 

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!

macro for copying cells above empty ones
 

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)

"JRB1024" wrote in message
ups.com...
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!

macro for copying cells above empty ones
 

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.


--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"JRB1024" wrote:

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!