Fixing problems in a slightly corrupt workbook

Gentlemen and ladies:

I am working with a spreadsheet I downloaded from a web site. Unfortunately, in this particular one (there are a series from this site), something went wrong. Colum A is supposed to be a year, but in quite a number of entries, column A was left blank, and the year was put into column B. There are several thousand rows in the spreadsheet and fixing this by hand would be laborious.

Is there some simple way to ask excel to put the value in column into column A if the value of column A is blank? Another possible approach would be to ask excel to copy column B into column A is that value is numeric--the misplaced years are the only numeric values in column B.

Thanks in advance.

DK

thank you. No, I do NOT need to shift all the columns in the row--on the contrary. I will try the second fix. Thanks.

DK

Originally Posted by daffodil11

Do you need the whole row to shift left if the year was entered into B? Is there other data?

If shifting left is okay, you could hightlight the range, hit F5, choose Special, choose Blanks, then goto Cell under Home tab and delete cells, shifting left.

If not, you could apply a short VB script.

Please Login or Register  to view this content.

That code goes in VB editor. Alt+F11, Insert, New Module, paste code. Adjust range of cells if too big or too small. Close that window.

Call macros with Alt+F8

That short piece of script just runs down a2:a25000, looks for blank cells, when it finds one, copies the adjacent cell into it, and clears that cell.

Alas, I did as you said with the script, and it came up to run when I did ALT-F8, but nothing happened. Is it possible that you forgot something? Thanks.

DK
FF

As it happens, I figured out a work-around of my own--I can just separate out those rows. Thank you for your time. I will have another question of a different type shortly.

David K