Excel guruosity required

[hornet]

Can any uber Excel types help with this please?

I've got a stonking great file I've imported into Excel, and I've managed to cobble together a macro (via keystroke recorder - can't do Visual Basic yet) that does loads of formatting work. However, there's one sticking point....I've got a list of 7 or so departments in col A, which then have info allocated to them in B, C and so on :-

A B C D
1 Dave
2
3
4 Bob
5
6
7 Chris
8
9

The actual file is usually about 450-500 rows deep, so manually copy/paste is a bit of a pain in the arse.

As part of the formatting macro, I'd like to tell Excel to copy what's in cell A1, then paste it into each subsequent blank cell going down the column until it finds a non blank, at which point it should copy what's in THAT cell into each subsequent cell etc etc. The end result should be that all the blanks under "Dave" get filled in, then "Bob" gets copied until the macro hits "Chris" and so on. I'm sure there's an IF in there somewhere, but I'm struggling to work out how to apply it.

Alternatively, if there's something I can insert into the macro in Visual Basic, I could use that then back engineer until I understand it.

Anyone help please?!

[docevi1]

change "10" to the number of entries you have to get this to work as a standalone macro, alternatively simply use the If statements & the variable usage in the Excel recorded stuff.

There will be better ways of doing it I'm sure, but I haven't done VBA for ages now and this works

------

newValue = "STARTING VALUE"
For x = 1 To 10
If Range("a" & x).Value = "" Then
Range("a" & x).Value = newValue
Else
newValue = Range("a" & x).Value
End If
Next x

[hornet]

Thanks. Can't pretend to understand Visual Basic (yet), but I've done a little macro using that, and it seems to work. Next task is to back engineer it so I know what on Earth is going on

Someone has just come up with this on another forum, which also seems to do the trick :-

Dim end_range As Integer
end_range = Sheets("sheet1".Cells(65536, 1).End(xlUp).Row

For Each c In Range("A1:A" & end_range)
If c.Value = "" Then c.Value = c.End(xlUp).Value
Next

Also had this formula suggested (enter in col B if data is in col A) :-

=if(A2="",B1,A2) (then copy down)

Kicking myself I couldn't work out the IF formula, as I'm usually quite handy with those - have some monster ISERROR/IF/VLOOKUP stuff strung together at work. I was expecting the answer to be horribly complicated, but it's actually elegantly simple.

Still, not to worry. Less time formatting Excel each month equals more opportunity to

[docevi1]

no worries, thats actually based upon standard programming techniques (the way I'd do it normally rather than the way to do it in Excel).

Using the if statement you have there you could easily use it in another column, copy & paste-special.

[wiggy001]

In C1, Enter 'Dave'
In C2, enter the formula =IF(B2="",C1,B2)
Copy C2 down to the end of your range

Column C should now contain your data (unless I've missed something?