moving cells

[jgregor]

Hi guys I have a question or should I say i have a big fu*** problem. Sory about that word ok one of my employee is getting every week a excel document looking like the picture you can see it. Now here is the problem. Each document has always at leas 1000 rows, so don't even think abaut moving cells manually

Now is it possible to move every second row (however it's not alwasy the second row ) to a row above in the firs empty cell. So is is possible to move cells in range A2:E2 in the row above that meen in first row in the firs empty cell in this case would be this range B1:F1... and so on for the next 1000 rows every week...

A B C D E
1 colours
2 green red purple yellow blue

 

[AnotherHiggins]

You want to take 1000 rows worth of data and put it in a single row?

Excel has 256 columns. That's it. 256.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[jgregor]

no i dont wont to put them all in the sam rows. its like this: for example. the 2nd row should go in first row right after the filled cell in the first empty cell.

4th should go in the 3rd row right after the filled cell in the first empty row...

 

[PHV]

Apart from the 256 columns limit, you wanted to use either the Transpose worksheet function or the Transpose named argument of the PasteSpecial method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

This will require VBA.

Start with the Macro Recorder and record yourself doing a few examples.

If you are unfamiliar with VBA, then this is going to be quite a task for you to pull off.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[jgregor]

yap that was my first idea to do it anf fortenately i am not i beginner in VBA. however i cannot do a few exemples ok I can but I olready know how does the code look like. And because somewher there is only 1 row free, somewhere 2 etc I can not just write down the vba code. somehow I should determine whic row should be moved and determine a destination in a row above. because sometimes the first free cell in the row above is the second, smt third...

tnx anyway guys

 

[AnotherHiggins]

If you can get all of the logic straight (what determines the row that is to be cut - what determines where it will be pasted) then we can help.

Basically you need to figure out how to state a rule that will always be true. Something like: always cut the first row that has more than X columns. Always paste in the nearest row before it where condition Y is met.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

Rather than cut 'n' paste, why not reorder the rows using a sort. You might need a sort helper column.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}