Macro to fill blank cells in column A based on non-blank cells

**ktalamantez** · 02-19-2014, 12:58 PM

I'm looking for help with creating a macro that will fill blank cells in column A with an Account number. In the attached sample, I would like the text in A6 to be copied from A7 to A54 (stopping at A54 because there's already text in A55), then copy the text in A55 from A56 to A98 (again stopping at A98 because there's already text in A99), then copy the text in A99 . . . and so on. The rows to copy from and to will change, so the macro needs to start with A6 but then only copy to the point where text is encountered and copy that new text forward. I can put the word END at the cell where I would want the macro to stop.

Thank you!

**icestationzbra** · 02-19-2014, 02:28 PM

what you are looking to have done should be fairly easily doable using standard functionality (no VBA required) - by accessing F5 > Specials > Blanks... BUT, it is not working on your sheet, because the blank cells on your worksheet aren't really behaving as blanks. so, here is a macro that mimics that functionality:

Please Login or Register  to view this content.

instructions for execution upon opening the file:

insert the above code into a new Module. then, navigate to ThisWorkbook > Workbook (from General list) > Open (from Declarations list), and call the above macro, so that it looks like:

Please Login or Register  to view this content.

now, as soon as macros are enabled, this macro should execute.

**JOHN H. DAVIS** · 02-19-2014, 03:58 PM

Another:

Please Login or Register  to view this content.

**AB33** · 02-19-2014, 04:55 PM

No loop code.

**ktalamantez** · 02-28-2014, 02:47 PM

Thank you so much, this is dramatically faster. ADDITIONAL QUESTION: Now that I've worked with a few different downloaded reports, I'm finding that the macro sometimes runs well past the end of the dataset. In the attached you'll see that the macro should have stopped at row 548, but it went all the way to row 4077. I can make my download put the word END in column A at the bottom of the dataset. Can the macro be revised to stop when it encounters the word END? Ultimately, I'd like to execute this macro when I open the file.