Variable Range Selection

[johngr55]

Hello and sorry if my question has been discussed again in past threads,

I have some data say in cells A1:C15. I want to use a macro which copies and pastes the data per row somewhere else in the spreadsheet. For example,

Copy a1:c1 and paste it at e1,
copy a2:c2 and paste it at e2...

etc...
up to row 15 where the data end. I know that this could be done using a range variable and a loop from 1 to 15, but i don't know how to select various ranges for each loop.. something like " for i = 1 to 15, cells (i ,1 : i ,3).select" ??

thanks for any help

[jerseyguy1996]

You could probably select the first row of cells that you want to copy and then in your macro say:

Please Login or Register  to view this content.

to let it know how many columns it should be copying on each iteration.



If you want to make the number of rows variable as well and not limit yourself to 15 you could select the entire range of cells and then use:

Please Login or Register  to view this content.

Instead of using R1C1 format you can also say:

Please Login or Register  to view this content.

Let me know if this is not clear.

[buran]

Hi,

@jerseyguy1996: if user selects the range that will be copied, it doesn't make sense to use macro to copy it - just use standard copy/paste interface offered by excel. also it is "bad" practice to select objects (i.e. using selection object) you are working with - it makes the macro work slowly. There are very little occasions when one will use selection.

@johngr55:
if I understand your problem you will have varying number of rows and you want to copy range starting from row 1 and including all rows of data. Is that correct? If there is no special reason it is not necessary to copy row by row. Here is the macro that will copy the range at once (You can change macro to fit your needs):

Please Login or Register  to view this content.

Let me know if this is what you want or if you want to copy row by row.

Buran

If you want

[jerseyguy1996]

Can you attach a sample workbook so that we will understand exactly what you are trying to do?

[johngr55]

Thanks for your answers but maybe I wasn't clear enough. The data i have look like this:

ColA ColB ColC
Row1 id1 5 3/3/09
Row2 id1 7 4/3/09
Row3 id2 8 5/3/09
Row4 id2 1 11/3/09
Row5 id3 11 6/3/09
Row6 id3 -5 20/3/09

first i have the id variable and then some other values (eg values in col. B, dates etc.)
I want to bring the data format into the following one:

id1 5 3/3/09 7 4/3/09
id2 8 5/3/09 1 11/3/09
id3 11 6/3/09 -5 20/3/09

I need to skip the row when it is the first appearance of an id and then for each of the following rows with the same id, I need to cut the whole row and paste it next to the first occured id.

I know how the mechanism is going to work and I have found a macro which does it, I am not sure though of how saying: when the row is correct -> copy range "row i / column A to row i / column C". In a few words, I don't know how to select the variable range from col.A to C but always depending on the id factor, and not just selecting the whole range from A1 to C6 for example.

I hope it's more clear now.
Thanks
John

[jerseyguy1996]

So for all id1's you want to have them in the same row and likewise for all id2's?

Can't this just be done with a pivot table?

[johngr55]

I am not sure how you can bring all the data for id1 for example in the same row using a pivot table. I think pt show you statistical measures not the actual data.. I might be missing something though ..

regardless of what I am trying to do.. if i wanted for example to copy cases where the row is even number or where the id = "john" do you know how can the range variate in the macro? if it was for example just 1 cell in A1 I would have said " cells(x,1).select " when the condition was met in row x. if I have 3 columns A-C how can I write: " cells( x1 : x3 ).select ??

thanks

[jerseyguy1996]

Will the id's all be grouped together? In other words will all of the "Johns" be grouped (regardless of whether there are 2 rows or 3 rows or will the "Johns" be separated by a "Bill" or something? Really the best way to crack this is to upload a sample copy of your spreadsheet so that we can get a good idea what you are trying to do. If you are worried about confidentiality just make up a sample spreadsheet using ficticious data.

P.S. you will probably get a spreadsheet back that contains working code that you can then just import into your actual data spreadsheet.

[teylyn]

jerseyguy, please don't quote whole posts. It's just clutter.