Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to blank cells
My column looks like this, I have data in D1, then cells D2:D10 are blank,
then data in D11 and so on. The data is spaced throughout the column (ie...not always the same # of blank cells between data). Also, the last cell in the range can vary from week to week. I need to copy the cell that has data to all blank cells below it until I get to the next nonblank cell. So if I have "A" in D1, "B" in D11 and cells D2:D10 are blank, I need to copy "A" into D2:D10, and then B into D12:D? (however many blank cells are below until the next cell with data). I have written something like this to work on a similar worksheet lr = Cells(Rows.Count, "D").End(xlUp).Row For Each c In Range("D1:D" & lr) If c = "" Then c.Value = c.Offset(-1) Next but it does not work with blank cells in this worksheet when I changed the statement to be If c = "" Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to blank cells
Your code works for me. I can only guess that your 'blank cells' are not
truly empty. -- Jim "scorpiorc" wrote in message ... | My column looks like this, I have data in D1, then cells D2:D10 are blank, | then data in D11 and so on. The data is spaced throughout the column | (ie...not always the same # of blank cells between data). Also, the last | cell in the range can vary from week to week. I need to copy the cell that | has data | to all blank cells below it until I get to the next nonblank cell. So if I | have "A" in D1, "B" in D11 and cells D2:D10 are blank, I need to copy "A" | into D2:D10, and then B into D12:D? (however many blank cells are below until | the next cell with data). | | I have written something like this to work on a similar worksheet | | lr = Cells(Rows.Count, "D").End(xlUp).Row | For Each c In Range("D1:D" & lr) | If c = "" Then c.Value = c.Offset(-1) | Next | | but it does not work with blank cells in this worksheet when I changed the | statement to be If c = "" | | Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to blank cells
See your other thread for more responses.
scorpiorc wrote: My column looks like this, I have data in D1, then cells D2:D10 are blank, then data in D11 and so on. The data is spaced throughout the column (ie...not always the same # of blank cells between data). Also, the last cell in the range can vary from week to week. I need to copy the cell that has data to all blank cells below it until I get to the next nonblank cell. So if I have "A" in D1, "B" in D11 and cells D2:D10 are blank, I need to copy "A" into D2:D10, and then B into D12:D? (however many blank cells are below until the next cell with data). I have written something like this to work on a similar worksheet lr = Cells(Rows.Count, "D").End(xlUp).Row For Each c In Range("D1:D" & lr) If c = "" Then c.Value = c.Offset(-1) Next but it does not work with blank cells in this worksheet when I changed the statement to be If c = "" Any ideas? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to blank cells
See response in your other thread. Gord Dibben MS Excel MVP On Wed, 6 Dec 2006 09:20:00 -0800, scorpiorc wrote: My column looks like this, I have data in D1, then cells D2:D10 are blank, then data in D11 and so on. The data is spaced throughout the column (ie...not always the same # of blank cells between data). Also, the last cell in the range can vary from week to week. I need to copy the cell that has data to all blank cells below it until I get to the next nonblank cell. So if I have "A" in D1, "B" in D11 and cells D2:D10 are blank, I need to copy "A" into D2:D10, and then B into D12:D? (however many blank cells are below until the next cell with data). I have written something like this to work on a similar worksheet lr = Cells(Rows.Count, "D").End(xlUp).Row For Each c In Range("D1:D" & lr) If c = "" Then c.Value = c.Offset(-1) Next but it does not work with blank cells in this worksheet when I changed the statement to be If c = "" Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I skip blank cells when writing formulas | Excel Worksheet Functions | |||
Counting blank cells until value is reached | Excel Worksheet Functions | |||
Excel inserted 0's in cells linked to blank cells | Excel Discussion (Misc queries) | |||
pivot tables reports - altering display of (blank) cells | Excel Worksheet Functions | |||
CONCATENATE problem with blank cells | Excel Discussion (Misc queries) |