I have a spreadsheet that was sent to me that lists Store Numbers, Item
Descriptions and QTY sold. The Spread sheet is 25000 lines long. The problem
i am coming across is the store number cells are merged together. When
unmerge the cells only the first Descrption next to the store number has a
store number associated with it. And everyone below it had a blank cell. So
the data is useless to me in Access because I need to have a store number for
each description. Is there a macro i can create or run that will paste the
first number in the column all the way down and then once is hits a new or
different store number it copies that number and pastes it in all the empty
cells below that, finds the next change in number, copies it and pastes....so
on and so forth. So the end result is there is a corresponding store number
with each product description.

One way. This will cycle through column A and fill blank cells with
the value from the previous cell until the last used row in the
spreadsheet is reached. Test on copy of data before running on
production data to make certain this is what you are after.
Sub this()
lRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To lRow
If Trim(Cells(i, 1).Offset(1)) = "" Then _
Cells(i, 1).Offset(1) = Cells(i, 1)
Next i
End Sub
Chuck wrote:
I have a spreadsheet that was sent to me that lists Store Numbers, Item
Descriptions and QTY sold. The Spread sheet is 25000 lines long. The problem
i am coming across is the store number cells are merged together. When
unmerge the cells only the first Descrption next to the store number has a
store number associated with it. And everyone below it had a blank cell. So
the data is useless to me in Access because I need to have a store number for
each description. Is there a macro i can create or run that will paste the
first number in the column all the way down and then once is hits a new or
different store number it copies that number and pastes it in all the empty
cells below that, finds the next change in number, copies it and pastes....so
on and so forth. So the end result is there is a corresponding store number
with each product description.

Assume your store number is in column A and you have contiguous data
in column B (I think that is what you mean after you unmerge the
cells), then highlight the cells in column A down to the bottom of
your data in B. Then press F5 (GoTo), click on Special and click on
Blanks. Only the blank cells in column A will now be highlighted, with
the active cell being the first empty cell. Then begin a formula by
typing = and then click in the cell above the active cell, then press
CTRL-Enter together. This will put that formula in every blank cell.

You can then highlight column A, click <copy, then Edit | Paste
Special | Values (check) | OK then <Enter in order to fix the values.

Hope this helps.

Pete

On Oct 8, 6:50 pm, Chuck wrote:
I have a spreadsheet that was sent to me that lists Store Numbers, Item
Descriptions and QTY sold. The Spread sheet is 25000 lines long. The problem
i am coming across is the store number cells are merged together. When
unmerge the cells only the first Descrption next to the store number has a
store number associated with it. And everyone below it had a blank cell. So
the data is useless to me in Access because I need to have a store number for
each description. Is there a macro i can create or run that will paste the
first number in the column all the way down and then once is hits a new or
different store number it copies that number and pastes it in all the empty
cells below that, finds the next change in number, copies it and pastes....so
on and so forth. So the end result is there is a corresponding store number
with each product description.

Column A is Store Number. And Column B is Descriptions of products sold in
that store. So an example would be A2-A200 is merged and it will say Store
100. And each cell from B2 to B200 will have a different Item description in
it that was sold in store 100. Now if i Unmerge Cell A2-A200 it will put 100
in Cell A2 and leave blanks down to Cell A200. I need to fill then with the
number 100 then When it hits Cell A201 which has a value of 110 in it to copy
that number and paste it from cell A202 down to whenever the store number
changes again.

"Pete_UK" wrote:

Assume your store number is in column A and you have contiguous data
in column B (I think that is what you mean after you unmerge the
cells), then highlight the cells in column A down to the bottom of
your data in B. Then press F5 (GoTo), click on Special and click on
Blanks. Only the blank cells in column A will now be highlighted, with
the active cell being the first empty cell. Then begin a formula by
typing = and then click in the cell above the active cell, then press
CTRL-Enter together. This will put that formula in every blank cell.

You can then highlight column A, click <copy, then Edit | Paste
Special | Values (check) | OK then <Enter in order to fix the values.

Hope this helps.

Pete

On Oct 8, 6:50 pm, Chuck wrote:
I have a spreadsheet that was sent to me that lists Store Numbers, Item
Descriptions and QTY sold. The Spread sheet is 25000 lines long. The problem
i am coming across is the store number cells are merged together. When
unmerge the cells only the first Descrption next to the store number has a
store number associated with it. And everyone below it had a blank cell. So
the data is useless to me in Access because I need to have a store number for
each description. Is there a macro i can create or run that will paste the
first number in the column all the way down and then once is hits a new or
different store number it copies that number and pastes it in all the empty
cells below that, finds the next change in number, copies it and pastes....so
on and so forth. So the end result is there is a corresponding store number
with each product description.

That was what I thought you meant and my procedure will give you what
you want - did you try it out?

Pete

On Oct 8, 10:36 pm, Chuck wrote:
Column A is Store Number. And Column B is Descriptions of products sold in
that store. So an example would be A2-A200 is merged and it will say Store
100. And each cell from B2 to B200 will have a different Item description in
it that was sold in store 100. Now if i Unmerge Cell A2-A200 it will put 100
in Cell A2 and leave blanks down to Cell A200. I need to fill then with the
number 100 then When it hits Cell A201 which has a value of 110 in it to copy
that number and paste it from cell A202 down to whenever the store number
changes again.



"Pete_UK" wrote:
Assume your store number is in column A and you have contiguous data
in column B (I think that is what you mean after you unmerge the
cells), then highlight the cells in column A down to the bottom of
your data in B. Then press F5 (GoTo), click on Special and click on
Blanks. Only the blank cells in column A will now be highlighted, with
the active cell being the first empty cell. Then begin a formula by
typing = and then click in the cell above the active cell, then press
CTRL-Enter together. This will put that formula in every blank cell.

You can then highlight column A, click <copy, then Edit | Paste
Special | Values (check) | OK then <Enter in order to fix the values.

Hope this helps.

Pete

On Oct 8, 6:50 pm, Chuck wrote:
I have a spreadsheet that was sent to me that lists Store Numbers, Item
Descriptions and QTY sold. The Spread sheet is 25000 lines long. The problem
i am coming across is the store number cells are merged together. When
unmerge the cells only the first Descrption next to the store number has a
store number associated with it. And everyone below it had a blank cell. So
the data is useless to me in Access because I need to have a store number for
each description. Is there a macro i can create or run that will paste the
first number in the column all the way down and then once is hits a new or
different store number it copies that number and pastes it in all the empty
cells below that, finds the next change in number, copies it and pastes....so
on and so forth. So the end result is there is a corresponding store number
with each product description.- Hide quoted text -

- Show quoted text -