Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a range of cells from a selection of cells
I currently have some code that searches a range for a specific value in one
column. When it finds that value the entire row is selected and added to range of rows using the Union method. When the search is over, the rows are deleted. I want to adapt the code to find the rows as it does, but then select the same range of columns within each row so that I can copy and paste them to another sheet. However, I can't paste rows into the cell I want to because the selections don't match. My dilemma is that I know how to capture the number of rows selected from the code, but don't know how to select the same number of rows on the paste sheet, starting at a specified row. Alternately, if I could select just the range of columns within the selected rows in the first place, that would help too. I looked at various angles, and finally tried this: rngFoundAll.Columns.Select = "B:F". Unlike previous attempts with Columns property syntax, it at least compiled, but generated Error 424 (object required) after running the code above it. If someone could help with this, as always, much appreciated. I think the second approach would be more elegant, but either one would accomplish what I'm looking for. Thanks again. Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a range of cells from a selection of cells
Unless you are not stating everything, it is not necessary
to know how many rows or columns are in the selection simply paste to a single cell range. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "danhattan" wrote in message ... I currently have some code that searches a range for a specific value in one column. When it finds that value the entire row is selected and added to range of rows using the Union method. When the search is over, the rows are deleted. I want to adapt the code to find the rows as it does, but then select the same range of columns within each row so that I can copy and paste them to another sheet. However, I can't paste rows into the cell I want to because the selections don't match. My dilemma is that I know how to capture the number of rows selected from the code, but don't know how to select the same number of rows on the paste sheet, starting at a specified row. Alternately, if I could select just the range of columns within the selected rows in the first place, that would help too. I looked at various angles, and finally tried this: rngFoundAll.Columns.Select = "B:F". Unlike previous attempts with Columns property syntax, it at least compiled, but generated Error 424 (object required) after running the code above it. If someone could help with this, as always, much appreciated. I think the second approach would be more elegant, but either one would accomplish what I'm looking for. Thanks again. Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a range of cells from a selection of Rows
My subject line may have caused confusion. It should have read "selection of
Rows". Because my code captures entire rows, all the way from column A to column IV, I can't paste into a range of cells. I have to paste into a corresponding number of selected rows. Thus my dilemma. Sorry about the typo in the subject line, and thanks for looking at it. That said, any additional thoughts on how to change the selection of entire rows to just a range of columns within the entire row? "David McRitchie" wrote: Unless you are not stating everything, it is not necessary to know how many rows or columns are in the selection simply paste to a single cell range. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "danhattan" wrote in message ... I currently have some code that searches a range for a specific value in one column. When it finds that value the entire row is selected and added to range of rows using the Union method. When the search is over, the rows are deleted. I want to adapt the code to find the rows as it does, but then select the same range of columns within each row so that I can copy and paste them to another sheet. However, I can't paste rows into the cell I want to because the selections don't match. My dilemma is that I know how to capture the number of rows selected from the code, but don't know how to select the same number of rows on the paste sheet, starting at a specified row. Alternately, if I could select just the range of columns within the selected rows in the first place, that would help too. I looked at various angles, and finally tried this: rngFoundAll.Columns.Select = "B:F". Unlike previous attempts with Columns property syntax, it at least compiled, but generated Error 424 (object required) after running the code above it. If someone could help with this, as always, much appreciated. I think the second approach would be more elegant, but either one would accomplish what I'm looking for. Thanks again. Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a range of cells from a selection of Rows
Hi Dan,
You would have to paste into a single cell in Column A, and the out receiving range would be expanded accordingly. You would have to paste to Column A because that is the only way the number of columns will match. "danhattan" wrote... My subject line may have caused confusion. It should have read "selection of Rows". Because my code captures entire rows, all the way from column A to column IV, I can't paste into a range of cells. I have to paste into a corresponding number of selected rows. Thus my dilemma. Sorry about the typo in the subject line, and thanks for looking at it. That said, any additional thoughts on how to change the selection of entire rows to just a range of columns within the entire row? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a range of cells from a selection of Rows
Thanks, David. Appreciate it.
"David McRitchie" wrote: Hi Dan, You would have to paste into a single cell in Column A, and the out receiving range would be expanded accordingly. You would have to paste to Column A because that is the only way the number of columns will match. "danhattan" wrote... My subject line may have caused confusion. It should have read "selection of Rows". Because my code captures entire rows, all the way from column A to column IV, I can't paste into a range of cells. I have to paste into a corresponding number of selected rows. Thus my dilemma. Sorry about the typo in the subject line, and thanks for looking at it. That said, any additional thoughts on how to change the selection of entire rows to just a range of columns within the entire row? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to save values from a range of cells based on selection from list | Excel Programming | |||
creation of name range without selection of cells | Excel Programming | |||
duplicate listbox selection to range of cells? | Excel Programming | |||
random selection from a range of cells | Excel Worksheet Functions | |||
Excel - Copy range of cells based on Combobox Selection | Excel Programming |