Forum Home
Press F1
Thread ID: 72881	2006-09-29 04:57:00	Excel problem 2	yorkshirekid (9033)	Press F1

Post ID	Timestamp	Content	User
488056	2006-09-29 04:57:00	I have six columns and over 900 rows . The data repeats itself (almost), every sixth row . Because it isn't exactly the same I cannot sort it . Sort is not an option . I need to take the data from row1 and place it in a new column of six . (call this column 1) . I am moving (copy/paste) the data . I then take the data from row 2 and place it in another new column of six . (call this column 2) . I then take the data from row 3 and 4 and 5 and do the same . Each row seperately . Each is a new column . AND THEN - I take the data from row 6 and place it in column 1 . Data from row 7 in column 2 Data from row 8 - 10 in columns 3 - 5, as above . AND THEN I repeat this from row 11 - 15, pasting the data into columns 1-5 again . It's a massive cut and paste exercise . There must be a faster way . If you can follow what I've just written - can anyone help me please . Thank you .	yorkshirekid (9033)
488057	2006-09-29 05:54:00	Hi, I assume that the location you are posting to is another sheet, or columns further over (not inside the copy columns). Also as I understand it you are copying a row of data from one place, and pasting it transposed (as a column rather than a row) to another range. When you copy say row 6 onwards do you start pasting that data below row 1 data rather than over the top of it? Should be able to write a macro to do what you need.	pico (4752)
488058	2006-09-29 23:42:00	Thanks, but you've misunderstood me. My fault. I'll try again. I don't need to transpose. I'm certain of this. I have a group of data (lets call it Group 1). It exists in six columns and six rows. I need to take the 2nd row and place it alongside the first row - in six new columns. So now I have 12 columns all on the same row. I take third row and place (move it with copy/paste) it in the 1st row (next to the second row of data I've just pasted). So now I have 18 columns. I do the same with the 4th, 5th and 6th rows. So - what did exist in a 6x6 format now exits in a 1x36 formats. One row and 36 columns. And that's for Group 1 data. I have 152 groups like that. Everything in the 1st row of each group has to be under each other. So, that's 152 rows of the first lines of data. Taking up 6 columns. Everything in the 2nd row has to be under each other. So that's 152 rows for the second lines of data. Taking up the NEXT 6 columns. And so on. And so on for each row of each Group. I've done over 1000 cut and pastes. I've tried a macro but can't get it to work. When I record it for Group 1, all is ok (it's recording and so it would be). When I run it (for Group 2), then Group 1 data is overwritten. It's driving me mad and has taken me hours. Help appreciated. Thank you.	yorkshirekid (9033)
488059	2006-09-30 09:15:00	Hi....I am from Doncaster, & You??? PJ:thumbs:	Poppa John (284)
488060	2006-10-01 06:48:00	Ay up our kid. The Shaymen come from my town. :cool:	yorkshirekid (9033)
488061	2006-10-01 07:24:00	Hi hopefully this is what you want. Note that Im still in the process of writing this as I wanted to cover the situation where the data may need to be split by number of cells rather than complete rows of data. You seem to be wanting to copy complete rows of data so should be fine. Sub MoveRowsToColumns() Dim Rng As Range, VarNumColumns As Variant, Rw As Long, Col As Integer, i As Integer Dim MsgUneven As Variant, blnIndCells As Boolean, c As Range, intCounter As Integer Dim rngMove As Range, LastRow As Long, LastCol As Integer, n As Long, RowStep As Long Dim FirstRow As Long, x As Long, y As Integer On Error Resume Next Set Rng = Application.InputBox("Select all the cells in the target range." & vbLf & vbLf & _ "Note - Do not select the header row", "Select Cells", Selection.Address, , , , , 8) If Err.Number <> 0 Then MsgBox "You must select the target cells", vbCritical, "Invalid Selection" Exit Sub End If On Error GoTo 0 VarNumColumns = Application.InputBox("How many columns should be populated?", _ "Columns Required", 36, , , , , 1) If VarNumColumns = False Then MsgBox "You must select the cells you want to move before running this macro", _ vbCritical, "Invalid Selection" Exit Sub ElseIf Val(VarNumColumns) <= Rng.Columns.Count Then MsgBox "You have entered to few columns. No data will be moved.", vbCritical, _ "Too Few Columns" Exit Sub ElseIf Val(VarNumColumns) Mod Rng.Columns.Count <> 0 Then MsgUneven = MsgBox("The number of columns required doesnt fit evenly with the" & _ vbLf & "number of columns per row. Do you want to mix rows?" & vbLf & vbLf & _ "Select YES to move individual cells, from left to right." & vbLf & _ "Select NO to cancel and try again.", vbYesNo + 256, "Uneven Column Numbers") If MsgUneven = vbYes Then blnIndCells = True Else RowStep = Val(VarNumColumns) / Rng.Columns.Count End If If blnIndCells = True Then 'Put in code to manage data that isnt in even columns Else 'Number of columns per row can be divided evenly into number of required columns For Rw = Rng(1).Row To (Rng.Rows.Count + Rng(1).Row) Step RowStep 'find last column in the matrix LastCol = Rng(1).Column + Rng.Columns.Count - 1 'find the last row in the matrix LastRow = Rw + RowStep - 1 'find first row in matrix FirstRow = Rw + 1 y = Rng(1).Column For x = FirstRow To LastRow y = y + Rng.Columns.Count Range(Cells(x, Rng(1).Column), Cells(x, LastCol)).Copy Cells(Rw, y) Cells(x, Rng(1).Column) = "~~~DeleteMe~~~" Next x Next Rw For n = Cells(65536, Rng(1).Column).End(xlUp).Row To Rng(1).Row Step -1 If Cells(n, Rng(1).Column) = "~~~DeleteMe~~~" Then Cells(n, Rng(1).Column).EntireRow.Delete End If Next n End If End Sub	Parry (5696)
488062	2006-10-02 17:37:00	Parry - It's 6.30am and you've already made my day. What a brilliant piece of code. I hope you didn't spend hours coding this for me. Whatever, I am most appreciative as it works PERFECTLY :-) Thank you.	yorkshirekid (9033)
488063	2006-10-02 20:06:00	My pleasure. Hopefully this has saved you a bit of time. Just be careful not to have data to the right of the cells as Im deleting whole rows at the end. You could change this to just deleting within the range if needed. regards, Graham	Parry (5696)
1