Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting ranges of cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting ranges of cells
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting ranges of cells
.. 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 =---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting ranges of cells
.. 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 =---- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting ranges of cells
.. 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 =---- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting ranges of cells
Thanks, Stan.
Worked like a charm. Dale "stanshoe" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting multiple ranges | Excel Programming | |||
Selecting ranges | Excel Programming | |||
Selecting two ranges of unadjacent cells | Excel Programming | |||
Selecting Named Ranges | Excel Programming | |||
selecting ranges | Excel Programming |