macro to fill blank cells with location

[kellen]

Hi,

I am trying to build a macro to fix a report so that location numbers will be beside all costs associated. It is likely best to display the data I have, and what I am looking to do.

Below I have provided an unedited report which is an example of what I am working with. Cell A1 will provide the location name (starting with 604 in example), and will have 1 to 7 rows of text in column B associated with that location, while column A will be blank until a there is a new location name. I would like to fill column A with location number beside each line of text (see edited report further below).

There is a blank row between each change in location name, then it will restart with a new location number and several rows of text.I do not need to keep the blank rows, but can anyone help with vba code to auto-fill column A with the location names?


UNEDITED REPORT
604 Text
(blank) Text
(blank) Text

605 Text

606 Text
(blank) Text


EDITED REPORT
604 Text
604 Text
604 Text

605 Text

606 Text
606 Text

[jaslake]

Hi kellen

Try this

Please Login or Register  to view this content.

Hope this helps.

John

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[kellen]

Great! Thanks John, this works perfectly.

[shg]

JB, that won't work correctly for a multi-area range.

[jaslake]

Hi kellen
Glad it works for you. If you're satisfied, please mark your post as solved. Click the scales if you feel it appropriate.

John

[JBeaucaire]

JB, that won't work correctly for a multi-area range.

It won't? It seemed to work fine for my test which was 3 separate areas, as per the OP's example. Can you reference an example of what you mean? I used a formula in the macro that takes into account the fact the a row might be blank, it skips those...

I like this approach merely for the elimination of looping row by row, the formula does the evaluation for you all at once.

[shg]

See attached.

[JBeaucaire]

SHG, how truly unexpected. The first line of code puts in the correct values for every row, but the second line of code somehow does NOT simply remove the formulas and leave the values behind, it seems to replicate the first formula result down the column!?

Any idea why?

Here's the corrected version for my approach, I guess I can't use the "WITH" method.

Please Login or Register  to view this content.

[shg]

Because you get the value from the first area of the range applied to all.

You second aproach converts the entire column to values, which may or may not be fine.

Here's what I use:

Please Login or Register  to view this content.

It should have code to check if there are more than 16K cells in the range, which may break SpecialCells in an unpleasant way.