Macro to find, copy, and paste until value change

[Guest]

On an imported worksheet I have a column that is mostly blanks with a company
number when the report changes. I would like a macro to go to the first
filled in cell, copy it and paste it into every subsequent blank cell until
the next company then copy and paste, etc to the bottom row. Ex:

Row-Value
1
2
3 - A
4
5
6 - B

Copy "A", paste to rows 4 and 5, copy "B", paste to following blank cells,
etc.

Thanks!

 

[John Michl]

Valerie -
Instead of a macro you could use a formula in a helper column. For
instance, if your value is in column A, then in cell B3 put the formula

= if (isblank(A3), B2, A3)

then copy this down. Once copied, perform a Copy - Paste Special
Values to remove the formulas.

- John Michl

 

[L. A. McClelland]

Valerie:

You can do this easily from the GUI without a macro.

1. Place your cursor in Row 3-A.
2. Hold the Shift key down and then use PageDown and/or the Arrow keys,
etc., to highlight the complete data range.
3. From the menu, select Edit, Go To, Special, Blanks.
4. With the "funny" range selected, build a formula:
a. Press "=" and then the UP arrow.
b. Hold the CTRL key down and then press ENTER.

Voila! All of the blank cells now have a formula that evaluate to the
Company number above.

5. Copy the range and use Paste Special, Values to convert the formulas to
values.

Now you can produce pivot tables or pivot charts to your heart's content.

HTH,

=Mac=

L. A. McClelland

 

[John Michl]

Mac -
That is very slick. I learn something new everyday. Thanks for
sharing. I've never used the Edit - Go To - Special functionality.

- John

 

[L. A. McClelland]

John:

You are very welcome. I, too, learn something every time I visit this
group.

=Mac=