Excel List question

  • Thread starter Thread starter ian
  • Start date Start date
I

ian

I have a list consisting of 5 columns of data, there are
some blank cells in the columns, i want the contents of
the cell directly above gaps to automatically fill the
gaps below it, ive tried to write a macro to do this but
failed.

Any ideas?
 
Hi

Select the column / range. Then Edit / Go to / Special / Blanks. This will
select all of the blank cells in the range. Type = and hit the up arrow and
then type Ctrl Enter. To fix these values use Copy then Paste Special /
Values.
 
No need for macro, select the range
press F5, click special, select blanks,
click OK. type the equal sign, press up arrow,
then finish by pressing ctrl + enter at the same time
done!
 
Ian,

This will do what you want, just adjust the value of X
depending on the number of rows:

Sub Replacements()

Dim X as integer
Dim Y as integer

For X = 1 to 1000
For y = 1 to 5

If cells (x,y).value = "" Then
Cells(x,y).value = cells (x-1,y).value
End if

Next Y
Next X

End Sub

'Cheers, Pete
 
Back
Top