Copying multiple cells down in a column with different data all at once

[BootleggerDave]

I have attached an example of the data I am working with. Basically what I am trying to do is copy each city name down to the start of the next city name. I know that if I was to select cell A1 and double click the black box in the corner it will copy down automatically to the next cell with data in it. What I'd like to do is do that at the same time for each cell in the column. The data I use can be very lengthy and having to select and double click every cell is very tedious. Any suggestions??

[hoyasaxa215]

Sure. Highlight Column A and then hit ctrl+G and click on 'Special'. Select 'Blanks' and click 'OK', which will select all the blank cells. Click on the formula bar at the top and enter =A1 and then (IMPORTANT) hit ctrl+enter. NOT just enter. That should work.

[hoyasaxa215]

You can then select the whole column and Copy it and then Paste Special > Values if you want to remove the equations.

[BootleggerDave]

Ok so I did that but now in all the blank cells it says #REF!

[hoyasaxa215]

It worked for me several times. Reread the instructions and make sure you didn't skip a step.

[BootleggerDave]

Ok let me try it again.

1) Highlight column A
2) Ctrl+G
3) Select "blanks"
4) type in formula bar =A1(IMPORTANT)
5) hit Ctrl+Enter

That's it correct?

[hoyasaxa215]

Sorry, you only type =A1

Then hit ctrl+enter.

[BootleggerDave]

BOOM! That worked!! Thank you!!