Selecting ranges of cells

[Dale Fye]

I just received a spreadsheet that contains about six columns.

The first two of these columns contain grouping information for the next six
columns, but only have the grouping level data in the first row of each group
of cells. I want to run a macro that will allow me to highlight a cell, have
it copy the contents of that cell and paste them in each cell below that one,
until the macro finds another cell that has data in it. It should then copy
that cell, and repeat the process.

I started out trying to just write the macro that will copy the current
selection, select the next group of rows that are blank, and paste the
result, but even that did not work. It looked like:

Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range("B471:B484").Select
ActiveSheet.Paste

But the third row in this code is an absolute reference to cells, rather
than removing a single row from the selection. If someone could advise me on
how to change this code so that the third line just deselects the last line
from the previous line of code, that would meet my needs.

Thanks in advance.

[stanshoe]

Dale-

You don't need a macro to do this. You can use the "Blanks" option under
the Edit/GoTo/Special menu to select the blank cells that you want to fill
and enter a simple formula to capture the data in the cell above.

Let's say I have data in Cells A1, B1, Cells A5,B5 and Cells A10,B10 and
want to fill in the blanks in the range A2:B15 with the value from above. I
would select the range A1:B15 then chose blanks under the Edit/GoTo/Special
menu. All of the blank cells in the range will be selected. In this
situation, the A2 is the active cell so I would enter the formula "=A1" in
cell A2. I would then hit the key combination Ctrl-Enter to enter the
formula in all of the blank cells. To get rid of the formulas, copy the
cells and use Paste Special values to paste the values back into the cells.

Stan Shoemaker
Palo Alto, CA

"Dale Fye" wrote:

>
> I just received a spreadsheet that contains about six columns.
>
> The first two of these columns contain grouping information for the next six
> columns, but only have the grouping level data in the first row of each group
> of cells. I want to run a macro that will allow me to highlight a cell, have
> it copy the contents of that cell and paste them in each cell below that one,
> until the macro finds another cell that has data in it. It should then copy
> that cell, and repeat the process.
>
> I started out trying to just write the macro that will copy the current
> selection, select the next group of rows that are blank, and paste the
> result, but even that did not work. It looked like:
>
> Selection.Copy
> Range(Selection, Selection.End(xlDown)).Select
> Range("B471:B484").Select
> ActiveSheet.Paste
>
> But the third row in this code is an absolute reference to cells, rather
> than removing a single row from the selection. If someone could advise me on
> how to change this code so that the third line just deselects the last line
> from the previous line of code, that would meet my needs.
>
> Thanks in advance.

[Dave D-C]

> .. contains about six columns ..
>The first two .. for the next six ..

How about instead of copy/paste:
For iRow = iRowFirst To iRowLast
if cells(iRow, iCol) <> "" then
zField = cells(iRow, iCol)
else
cells(iRow, iCol) = zField
End If
Next iRow

Dale wrote:
>I just received a spreadsheet that contains about six columns.
>
>The first two of these columns contain grouping information for the next six
>columns, but only have the grouping level data in the first row of each group
>of cells. I want to run a macro that will allow me to highlight a cell, have
>it copy the contents of that cell and paste them in each cell below that one,
>until the macro finds another cell that has data in it. It should then copy
>that cell, and repeat the process.
>
>I started out trying to just write the macro that will copy the current
>selection, select the next group of rows that are blank, and paste the
>result, but even that did not work. It looked like:
>
> Selection.Copy
> Range(Selection, Selection.End(xlDown)).Select
> Range("B471:B484").Select
> ActiveSheet.Paste
>
>But the third row in this code is an absolute reference to cells, rather
>than removing a single row from the selection. If someone could advise me on
>how to change this code so that the third line just deselects the last line
>from the previous line of code, that would meet my needs.
>
>Thanks in advance.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Dave D-C]

> .. contains about six columns ..
>The first two .. for the next six ..

How about instead of copy/paste:
For iRow = iRowFirst To iRowLast
if cells(iRow, iCol) <> "" then
zField = cells(iRow, iCol)
else
cells(iRow, iCol) = zField
End If
Next iRow

Dale wrote:
>I just received a spreadsheet that contains about six columns.
>
>The first two of these columns contain grouping information for the next six
>columns, but only have the grouping level data in the first row of each group
>of cells. I want to run a macro that will allow me to highlight a cell, have
>it copy the contents of that cell and paste them in each cell below that one,
>until the macro finds another cell that has data in it. It should then copy
>that cell, and repeat the process.
>
>I started out trying to just write the macro that will copy the current
>selection, select the next group of rows that are blank, and paste the
>result, but even that did not work. It looked like:
>
> Selection.Copy
> Range(Selection, Selection.End(xlDown)).Select
> Range("B471:B484").Select
> ActiveSheet.Paste
>
>But the third row in this code is an absolute reference to cells, rather
>than removing a single row from the selection. If someone could advise me on
>how to change this code so that the third line just deselects the last line
>from the previous line of code, that would meet my needs.
>
>Thanks in advance.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Dave D-C]

> .. contains about six columns ..
>The first two .. for the next six ..

How about instead of copy/paste:
For iRow = iRowFirst To iRowLast
if cells(iRow, iCol) <> "" then
zField = cells(iRow, iCol)
else
cells(iRow, iCol) = zField
End If
Next iRow

Dale wrote:
>I just received a spreadsheet that contains about six columns.
>
>The first two of these columns contain grouping information for the next six
>columns, but only have the grouping level data in the first row of each group
>of cells. I want to run a macro that will allow me to highlight a cell, have
>it copy the contents of that cell and paste them in each cell below that one,
>until the macro finds another cell that has data in it. It should then copy
>that cell, and repeat the process.
>
>I started out trying to just write the macro that will copy the current
>selection, select the next group of rows that are blank, and paste the
>result, but even that did not work. It looked like:
>
> Selection.Copy
> Range(Selection, Selection.End(xlDown)).Select
> Range("B471:B484").Select
> ActiveSheet.Paste
>
>But the third row in this code is an absolute reference to cells, rather
>than removing a single row from the selection. If someone could advise me on
>how to change this code so that the third line just deselects the last line
>from the previous line of code, that would meet my needs.
>
>Thanks in advance.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Dale Fye]

Thanks, Stan.

Worked like a charm.

Dale
"stanshoe" <stanshoe@discussions.microsoft.com> wrote in message
news:47B3BB42-965D-44F2-B451-12A1A7CAC306@microsoft.com...
> Dale-
>
> You don't need a macro to do this. You can use the "Blanks" option under
> the Edit/GoTo/Special menu to select the blank cells that you want to fill
> and enter a simple formula to capture the data in the cell above.
>
> Let's say I have data in Cells A1, B1, Cells A5,B5 and Cells A10,B10 and
> want to fill in the blanks in the range A2:B15 with the value from above.
> I
> would select the range A1:B15 then chose blanks under the
> Edit/GoTo/Special
> menu. All of the blank cells in the range will be selected. In this
> situation, the A2 is the active cell so I would enter the formula "=A1" in
> cell A2. I would then hit the key combination Ctrl-Enter to enter the
> formula in all of the blank cells. To get rid of the formulas, copy the
> cells and use Paste Special values to paste the values back into the
> cells.
>
> Stan Shoemaker
> Palo Alto, CA
>
> "Dale Fye" wrote:
>
>>
>> I just received a spreadsheet that contains about six columns.
>>
>> The first two of these columns contain grouping information for the next
>> six
>> columns, but only have the grouping level data in the first row of each
>> group
>> of cells. I want to run a macro that will allow me to highlight a cell,
>> have
>> it copy the contents of that cell and paste them in each cell below that
>> one,
>> until the macro finds another cell that has data in it. It should then
>> copy
>> that cell, and repeat the process.
>>
>> I started out trying to just write the macro that will copy the current
>> selection, select the next group of rows that are blank, and paste the
>> result, but even that did not work. It looked like:
>>
>> Selection.Copy
>> Range(Selection, Selection.End(xlDown)).Select
>> Range("B471:B484").Select
>> ActiveSheet.Paste
>>
>> But the third row in this code is an absolute reference to cells, rather
>> than removing a single row from the selection. If someone could advise
>> me on
>> how to change this code so that the third line just deselects the last
>> line
>> from the previous line of code, that would meet my needs.
>>
>> Thanks in advance.