i have a large spreadsheet with missing data. Not all cells are fully
populated but the info is the same as the column above. how can i populate
the missing cells by copying the previous cell (where there is info) without
doing it manually. sample of data hereunder

Date
01.02.2006



20.03.2006



06.04.2006


07.04.2006



15.04.2006

please help. thanks in advance

Hi,

Insert a 'helper column' to the right of you data then assuming your data
are in A1 down put this in B1
=A1
Then put this in B2
=IF(A2="",B1,A2)
Drag down to the same length as Column A
Copy the helper column then in A1
Edit|Paste special
Select 'Values' and click OK
Delete the helper column

Mike

"vonoise" wrote:

i have a large spreadsheet with missing data. Not all cells are fully
populated but the info is the same as the column above. how can i populate
the missing cells by copying the previous cell (where there is info) without
doing it manually. sample of data hereunder

Date
01.02.2006



20.03.2006



06.04.2006


07.04.2006



15.04.2006

please help. thanks in advance

one way is to select the whole range and use this macro:

Sub cus()
For Each cell In Selection
If Len(cell) 0 Then
cell.Offset(1, 0) = cell.Value
End If
Next cell
End Sub

another - presume yr data is in col A (starting in A1), then in B2 put
the following formula:
=IF(LEN(A1)0,A1,"")
copy down
then select select everything starting from B2 down, copy, go to A2
and paste special as values

Mike is right

the formula should read

=IF(LEN(A1)0,A1,B2)

I'm sure you really meant this :)

Sub cus()
For Each cell In Selection
If Len(cell.Offset(1, 0)) = 0 Then
cell.Offset(1, 0) = cell.Value
End If
Next cell
End Sub

Mike

Mike

"Jarek Kujawa" wrote:

one way is to select the whole range and use this macro:

Sub cus()
For Each cell In Selection
If Len(cell) 0 Then
cell.Offset(1, 0) = cell.Value
End If
Next cell
End Sub

another - presume yr data is in col A (starting in A1), then in B2 put
the following formula:
=IF(LEN(A1)0,A1,"")
copy down
then select select everything starting from B2 down, copy, go to A2
and paste special as values

right, Mike
must be still asleep,though it is almost noon, here
;-)))
I was just balding my head and thinking "why all cells are populated
with 01.02.2006?"
came here and saw your answer
SORRY
thks

thanks Mike & Jarek, u just saved me tons of hours of work

"Jarek Kujawa" wrote:

right, Mike
must be still asleep,though it is almost noon, here
;-)))
I was just balding my head and thinking "why all cells are populated
with 01.02.2006?"
came here and saw your answer
SORRY
thks