[Solved] Quickly replace blank cells with value above

[moominboy]

Hi guys! Thanks in advance for any help or info.

I have searched the forums but haven't found what I'm looking for. I have a spreadsheet with details of deliveries, but the format it is in just now isn't right for what I need.

Say row1 starts with the appropriate driver id, name, vehicle, delivery address etc. Just now, it is only the first row which has the driver information like so...

Elan Eslie PY56 BZR 332794660 2004 Bathykolpian Services Theurgy Industrial A Normal


What I would like to do is fill in all the blanks under the drivers appropriate columns with the value above it. I started by doing a2=a1 the dragging and pasting but there's a hell of a lot to do. Is this possible in OO? I know how it's done in Excel but don't have MS Office on this pc.

Sorry if the details I've put in are cramped, I can't figure out how to do a table! :/

[JohnSUN-Pensioner]

May be so?

[crusader]

I know how it's done in Excel but don't have MS Office on this pc. Sorry if the details I've put in are cramped, I can't figure out how to do a table! :/

If you are referring to the Ctrl+d option in Excel, it is available in LibreOffice; I don't know about OO.
Else, you may wish to specify how you do it in Excel and see if that option is available in OO/LO.

[moominboy]

Thanks for the replies folks!

JohnSun, Just reading up on your link now, but thought I'd reply quickly for Crusaders' question!

This seems to be a concise way of explaining, far better than I could.

EDIT; Thanks JohnSUN, but that's not exactly what I was after. Thought I should probably show a picture, worth a thousand and all that...

So basically, I'm wanting to be able to somehow highlight all those columns which have blanks in them, probably by ctrl+click each header, then do some magic and fill in a formula so each blank copies the drivers details above it.

[gerard24]

Not so quickly, but :

1. Select the whole area (cells with content and blank cells, ex : A1:A50)
2. Ctrl+C
3. Select the area from 1st blank cell till the end, ex : A3:A50
4. Press F2, then write =A2 (or A49) 'cell above cell with focus
5. Alt+Enter
6. Select again the whole area
7. Ctrl+Shift+V
8. Check 'text', 'number', may be 'format' (what you want to copy) and 'skip empty cells'.
9. Ok.

If you want to remove formula, make again a paste special unchecking formula.

[Villeroy]

Install the macro package suggested by happy JohnSUN.
Select the whole area and call Tools>Macros>Organize>Basic... MyMacros>DragQueen>cpCells>FillBlanksDown (or some shortcut to that macro)

[moominboy]

Ah! My fault, sorry JohnSUN! That's what I get for only reading half of a thread. Thanks for all your replies guys!