Excel - copy value down list 1

[richand1]

I have a list of data:

Unit Number Period
GB045543 2006/01
2006/02
2006/03
2006/04
GB300994 2006/01
GB329025 2006/03

This is just a small snapshot of the data list - it goes all the way down to 4,500 or so rows.

As you can see, there will be many unit numbers in column 1 and a random selection of period numbers in column 2, and then where there are more than one period number per unit, a load of blank cells underneath the unit number.
What I need to do is copy down the unit number next to each period, without overwriting the next one, so I need a piece of code that will copy the unit number down the report, stopping where there is a new unit number and then to copy the new one down. And so on and so forth.

Can someone help please because this is driving me mad!!

Thank you.
Richard

 

[Bong]

There are probably more elegant ways to do it but I would just go brute force:

lastrow=activesheet.usedrange.rows.count
un=cells(1,1).value
for i=2 to lastrow
 if (cells(i,1).value = "") then
   cells(i,1)=un
 else 
   un=cells(i,1)
 end if
next

_________________
Bob Rashkin

 

[richand1]

Absolutely magic.

Thank you very much Bob.

 

[AnotherHiggins]

Here's a shorter way that should run faster for several thousand rows:

Range("A1:A" & Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row). _
    SpecialCells(xlCellTypeBlanks) = "=R[-1]C"

If you want to replace the formulas with values, then add the second line of code:

Range("A1:A" & Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row). _
    SpecialCells(xlCellTypeBlanks) = "=R[-1]C"
[A:A].Value = [A:A].Value

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.